Conditional formatting VBA multiple conditions

Question

I am extremely new to VBA world and need some assistance with the VBA side of conditional formatting.

1) I need conditional formatting to be applied to column (M)

green under 7
yellow from 7-20
red greater than 20

With the overriding condition that if column (N) if it states NOPO, I do not want conditional formatting to be applied.

I have worked out a formula to use that indicates what colour is required but unable to turn that into VBA conditional formatting (this formula shows what colour and if the conditional formatting should be applied.

=IF(N2="osno",IF(M2<=7,"green",IF(M2<7,IF(M2>20,"red","less than 20"),IF(M2>20,IF(M2>20,"red","less than 20"),"yellow"))),"no format")

This is my current VBA script, as you can no doubt see it's very messy and was from a recorded script.

    Sub Conditional()
'
' Notification_05 Macro
' Conditional Formatting
'

'
    Sheets("Final").Select
    Columns("M:M").Select

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=8"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=8", Formula2:="=20"
    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:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=20"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 470000
        .TintAndShade = 0
    ActiveWindow.SmallScroll Down:=-27
    Range("M2").Select
    With Range("M:M")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=LEN(TRIM(M1))=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
                End With
                   End With
                   End With
End Sub

Thanks,

Blake

Your =IF(N2="osno",IF(M2<=7,"green",IF(M2<7,IF(M2>20,"red","less than 20"),IF(M2>20,IF(M2>20,"red","less than 20"),"yellow"))),"no format") is equivalent to =IF(N2="osno",IF(M2<=7,"green",IF(M2>20,"red","yellow")),"no format") because the M2<7 part will always evaluate to False and the second M2>20 will always be True if it gets past the first M2>20. That doesn't help you write it as VBA code, but it might simplify what you think you need to do. — YowE3K

Tim Williams Tim Williams · Accepted Answer · 2016-10-24T03:40:15

A CF formula needs to return either true or false: you can't use a single formula to assign one of multiple colors, only to decide if a color should be applied or not. You will need three rules, each with a slightly different formula.

Sub Tester()

    Dim rng As Range

    Set rng = Selection

    rng.FormatConditions.Delete 'clear any existing rules

    AddRule rng, "=AND(M2=""osno"", N2<7)", vbGreen
    AddRule rng, "=AND(M2=""osno"", N2>=7,N2<=20)", vbYellow
    AddRule rng, "=AND(M2=""osno"", N2>20)", vbRed

End Sub

'utility sub: add a CF rule given the formula and a fill color
Sub AddRule(rng, sFormula, lColor)
    With Selection.FormatConditions
        With .Add(Type:=xlExpression, Formula1:=sFormula)
            .Interior.Color = lColor
            .StopIfTrue = True
        End With
    End With
End Sub

Conditional formatting VBA multiple conditions

1 Answers