Can someone tell me why this doesn't work.

[LutonBarry]

Hello folks,

Please refer to the attached macro enabled spreadsheet. I want to format the rows based on the value in Column L on the following basis:

If L is <35% colour the row Green.
If L less <75% >=35% then Amber
If L >=75% then Red.

The code I've written works on the rows that only contain data but colours all rows Red, Green or Amber regardless of the value in Column L.



Where have I gone wrong?

Thanks.

[Paul_Hossler]

Try this

Option Explicit

Sub CondFormat()
    
    With Sheets("Jeopardy").Columns("A:L")
        .FormatConditions.Delete
        
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1<.75)"
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1<.35)"
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1>=.75)"
        
        'Colours SR-4 and <75% rows Amber
        With .FormatConditions(1)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 49407 'Amber
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
        
        'Colours SR-4 and <35% rows Green
        With .FormatConditions(2)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936 'Green
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
        
        'Colours SR-4 and >=75% rows Red
        With .FormatConditions(3)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255 'Red
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    End With
End Sub

[LutonBarry]

Paul,

Many thanks for that. It's done the trick.

If I wanted to remove the reference to column B

Would I modify the formula from

.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1<.75)"

To
.FormatConditions.Add Type:=xlExpression, Formula1:="=($L1<.75)"

[Paul_Hossler]

Not sure I understand, but try it and see if it's what you want