1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3436683
  • 11
  • 1
  • 2

2 Answers2

2

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.

L42
  • 19,427
  • 11
  • 44
  • 68
0

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.

Community
  • 1
  • 1
pintxo
  • 2,085
  • 14
  • 27
  • hey thanks so much for the quick answer! i tried it but it says there is something wrong with the formula. I also tried for one condition only =INDIRECT("B" & ROW())="""" but it did not highlight the row in green. what do you think i can do to troubleshoot this? – user3436683 Mar 19 '14 at 09:25
  • @user3436683 you can debug the formula directly in the sheet to check they are working properly – pintxo Mar 19 '14 at 10:31
  • @user3436683 I corrected the formula as it seem to be impossible to use AND() in a conditional formatting. – pintxo Mar 19 '14 at 10:43