Conditional Formatting Macro

[carrieebacon]

I am trying to apply the following conditional formatting to a macro. It is creating the formulas and applying them to the correct range, but it is only setting the format for the first formula. Any ideas?

This is my code:
Sub CondishFormatMulti()
Dim MyRange As Range
Set MyRange = Range("$E:$E, $I:$I, $M:$M, $Q:$Q")
MyRange.FormatConditions.Delete
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=0.1", Formula2:="=0.5"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 230, 153)
MyRange.FormatConditions(1).Font.Color = RGB(128, 96, 0)
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=-0.1", Formula2:="=-0.5"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 230, 153)
MyRange.FormatConditions(1).Font.Color = RGB(128, 96, 0)
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=0.001", Formula2:="=0.1"
MyRange.FormatConditions(1).Interior.Color = RGB(198, 224, 180)
MyRange.FormatConditions(1).Font.Color = RGB(55, 86, 35)
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=-0.001", Formula2:="=-0.1"
MyRange.FormatConditions(1).Interior.Color = RGB(198, 224, 180)
MyRange.FormatConditions(1).Font.Color = RGB(55, 86, 35)
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0.5"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
MyRange.FormatConditions(1).Font.Color = RGB(0, 0, 0)
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=-0.5"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
MyRange.FormatConditions(1).Font.Color = RGB(0, 0, 0)
End Sub

 

[kevin9999]

but it is only setting the format for the first formula.

Because your code is written as

MyRange.FormatConditions(1).

in each case. Try writing each condition by incrementing the conditions: (2), (3) etc.

 

[rlv01]

It's because you are making all the changes to only the first format condition. Here is an alternative approach.

Sub CondishFormatMulti()
    Dim MyRange As Range


    Set MyRange = Range("$E:$E, $I:$I, $M:$M, $Q:$Q")
    MyRange.FormatConditions.Delete

    With MyRange.FormatConditions
        With .Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.1", Formula2:="=0.5")
            .Interior.Color = RGB(255, 230, 153)
            .Font.Color = RGB(128, 96, 0)
        End With

        With .Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-0.1", Formula2:="=-0.5")
            .Interior.Color = RGB(255, 230, 153)
            .Font.Color = RGB(128, 96, 0)
        End With

        With .Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.001", Formula2:="=0.1")
            .Interior.Color = RGB(198, 224, 180)
            .Font.Color = RGB(55, 86, 35)
        End With

        With .Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-0.001", Formula2:="=-0.1")
            .Interior.Color = RGB(198, 224, 180)
            .Font.Color = RGB(55, 86, 35)
        End With

        With .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0.5")
            .Interior.Color = RGB(255, 0, 0)
            .Font.Color = RGB(0, 0, 0)
        End With

        With .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=-0.5")
            .Interior.Color = RGB(255, 0, 0)
            .Font.Color = RGB(0, 0, 0)
        End With
    End With
End Sub