Conditionally formatting cells in VBA

Question

I am conditionally formatting a range of cells. I want to do greater than and less than another cell value. I have found this code online that lets me do that but I was wondering if there is a way to skip formatting if the condition cell is blank.

Example: I want to highlight "G14" red if the value is greater than "D14"+"E14". Vise versa for minus. If the value of "E14" is blank it still make s it red because it is acting as if it is zero.

Here is my code:

'Format Measured
 Dim rg As Range
 Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
 Set rg = Range("G14", Range("G14").End(xlDown))

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$D14+$E14+$H14")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$D14-$F14")

'define the format applied for each conditional format
With cond1
.Font.FontStyle = "Bold"
.Font.Color = vbRed
End With

With cond2
.Font.FontStyle = "Bold"
.Font.Color = vbRed
End With

Hm, maybe different routes here. I'm thinking along the lines of SpecialCells or an adaptation of your formula to include "<>""" — JvdV
So should the conditional formatting check if either D14 or E14 is blank, or just E14? — BigBen
Just E14. The way I have my spreadsheet is I have a measurement in D14 and a tolerance in E14 and F14. Some measurements do not have tolerances. — Brandon Campbell

BigBen BigBen · Accepted Answer · 2019-11-20T15:13:16

Proposing the alternate solution to use a formula:

Sub Test()
    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
    Set rg = Range("G14", Range("G14").End(xlDown))

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlExpression, , "=AND($G14>$D14+$E14,$E14<>"""")")
    Set cond2 = rg.FormatConditions.Add(xlExpression, , "=AND($G14<$D14-$E14,$E14<>"""")")

    'define the format applied for each conditional format
    With cond1
        .Font.FontStyle = "Bold"
        .Font.Color = vbRed
    End With

    With cond2
        .Font.FontStyle = "Bold"
        .Font.Color = vbRed
    End With
End Sub

Note that if the format is the same for each rule, then you could combine the two into one.

Conditionally formatting cells in VBA

2 Answers