Need help with multiple conditional formatting

[butters149]

Hello,

I am trying to modify my VBA to do multiple conditional formatting but it doesn't seem to work. Below is the code and the two outcomes I like. Can anyone please help? Thank you.

1.) Highlight range of row cells if column R is more than 60
AND
2.) Highlight range of row cells if column R and L meet criteria of $R2>45,$L2>100000

Range("E2:R2").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$R2>60"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($R2>45,$L2>100000)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select

 

[Joe4]

It looks like you have only written the formulas to apply to row 2 only.
How many rows do you want to apply this to?
How can we determine which row to go down to?

 

[butters149]

It looks like you have only written the formulas to apply to row 2 only.
How many rows do you want to apply this to?
How can we determine which row to go down to?

Ah ok I wanted it from columns E2 through R2, excluding the first row because of the title and go down until no more available data.

 

[Joe4]

For rows with data, does column E always have something in it?
If not, is there some other column that will always have some value in it for all the non-blank rows?

 

[Joe4]

Assuming that column E can be used, then try this code (both conditions can be combined into one, since they are applying the same format):

    With Range("E2:R" & Cells(Rows.Count, "E").End(xlUp).Row)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($R2>60,AND($R2>45,$L2>100000))"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

 

[butters149]

Assuming that column E can be used, then try this code (both conditions can be combined into one, since they are applying the same format):

    With Range("E2:R" & Cells(Rows.Count, "E").End(xlUp).Row)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($R2>60,AND($R2>45,$L2>100000))"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

Thank you! this worked.

 

[Joe4]

You are welcome.