Conditional formatting using 3 conditions in Macro/VBA

Question

I would like to program a macro in Excel that would highlight the entire row green if the following conditions are met:

Emergency Type (Column B) = """"
In Window (Column N) = "On time"
Status (Column O) = "Successful"

The range is dynamic, but for the purpose of this example it is A2:P236.
I would appreciate any help on this. Thanks.

Answer 1

Programmatically? Try this:

Sub ApplyConditionalFormatting()

Dim applyto As Range

Set applyto = Range("A2:P236") 'you can make this dynamic

Range("A2").FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AND($B2="""",$N2=""On Time"",$O2=""Successful"")"
With Range("A2").FormatConditions(1)
    .SetFirstPriority
    With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    .ModifyAppliesToRange applyto
    .StopIfTrue = False
End With

End Sub

HTH.

Answer 2

Use the conditional formatting functionality of Excel. Use the formula based formatting. Your formula should look like:

=(INDIRECT(ADDRESS(ROW(), 2))="""""") *
 (INDIRECT(ADDRESS(ROW(), 3))="On time") *
 (INDIRECT(ADDRESS(ROW(), 4))="Successful")

This will select the Column X of the current row:

=INDIRECT(ADDRESS(ROW(), X))

It is not necessary to use INDIRECT in conditional formatting, but it makes live easier as Excel has some "issues" with conditional formatting and copy and past operations.

This ensure it only returns true in case all conditions are met:

= bool * bool

According to Conditional formatting using AND() function it is not possible to use AND() in a conditional formatting. But multiplying boolean values is possible and works like a charm.