VBA Conditional Format Error 9

[mattadams84]

Hi,

I am trying to apply conditional formatting using VBA. I have got this code to work successfully :

Sub ResetCF()Application.ScreenUpdating = False
    With ActiveSheet
    
          With .Range("$E:$R")
            'Blank Cells
            .FormatConditions.Add xlExpression, Formula1:="=$E1="""""
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions(1).StopIfTrue = False
        End With
        
         With .Range("$E:$E,$G:$G,$I:$I,$k:$k,$m:$m,$o:$o,$q:$q,$s:$s")
            'Borders
            .FormatConditions.Add xlExpression, Formula1:="=$E1<>"""""
            With .FormatConditions(2).Borders(xlLeft)
                 .LineStyle = xlContinuous
                 .ColorIndex = xlAutomatic
                 .TintAndShade = 0
                 .Weight = xlThin
                End With
            .FormatConditions(2).StopIfTrue = False
        End With
        
        With .Range("$G:$G")
            'green
            .FormatConditions.Add xlExpression, Formula1:="=$G1<$E1-2"
            .FormatConditions(3).Interior.Color = RGB(198, 239, 206)
            .FormatConditions(3).Font.Color = RGB(0, 97, 0)
            .FormatConditions(3).StopIfTrue = False
        End With
        
        With .Range("$G:$G")
            'red
            .FormatConditions.Add xlExpression, Formula1:="=$G1>$E1+2"
            .FormatConditions(4).Interior.Color = RGB(255, 199, 206)
            .FormatConditions(4).Font.Color = RGB(156, 0, 6)
            .FormatConditions(4).StopIfTrue = False
        End With
        
        With .Range("$G:$G")
            'yellow
            .FormatConditions.Add xlExpression, Formula1:="=OR($G1<$E1+2,$G1>$E1-2)"
            .FormatConditions(5).Interior.Color = RGB(255, 235, 156)
            .FormatConditions(5).Font.Color = RGB(156, 101, 0)
            .FormatConditions(5).StopIfTrue = False
        End With
        
        
    End With
Application.ScreenUpdating = True
End Sub

The problem i am having is that i now want to add the same conditional formatting (red,green,yellow) to column H but that uses a different formula. So what i did was add this :

With .Range("$H:$H")            
            'green
            .FormatConditions.Add xlExpression, Formula1:="=$H1<$F1-2"
            .FormatConditions(6).Interior.Color = RGB(198, 239, 206)
            .FormatConditions(6).Font.Color = RGB(0, 97, 0)
            .FormatConditions(6).StopIfTrue = False
        End With
        
        With .Range("$H:$H")
            'red
            .FormatConditions.Add xlExpression, Formula1:="=$H1>$F1+2"
            .FormatConditions(7).Interior.Color = RGB(255, 199, 206)
            .FormatConditions(7).Font.Color = RGB(156, 0, 6)
            .FormatConditions(7).StopIfTrue = False
        End With
        
        With .Range("$H:$H")
            'yellow
            .FormatConditions.Add xlExpression, Formula1:="=OR($H1<$F1+2,$H1>$F1-2)"
            .FormatConditions(8).Interior.Color = RGB(255, 235, 156)
            .FormatConditions(8).Font.Color = RGB(156, 101, 0)
            .FormatConditions(8).StopIfTrue = False
        End With

The problem i am getting is on this line :

.FormatConditions(6).Interior.Color = RGB(198, 239, 206)

I get an error 9 message saying subscript is out of range. I guess it is because the "Look" of the conditional formatting is the same for FormatCondtions(3). Is there any way around this? Perhaps i could define the look somewhere and then apply it to different columns bearing in mind that each column will have a different formula...

[Jan Karel Pieterse]

The problem is with the counter. Try like so:

With .Range("$H:$H").FormatConditions.Add(xlExpression, Formula1:="=$H1<$F1-2")
            'green
            
            .Interior.Color = RGB(198, 239, 206)
            .Font.Color = RGB(0, 97, 0)
            .StopIfTrue = False
        End With
        
        With .Range("$H:$H").FormatConditions.Add(xlExpression, Formula1:="=$H1>$F1+2")
            'red
            .Interior.Color = RGB(255, 199, 206)
            .Font.Color = RGB(156, 0, 6)
            .StopIfTrue = False
        End With
        
        With .Range("$H:$H").FormatConditions.Add(xlExpression, Formula1:="=OR($H1<$F1+2,$H1>$F1-2)")
            'yellow
            .Interior.Color = RGB(255, 235, 156)
            .Font.Color = RGB(156, 101, 0)
            .StopIfTrue = False
        End With

[rlv]

You also have a housekeeping problem. As-is, the first time you run ResetCF you will have 5 format conditions. The 2nd time, you will have 10, 3rd... 15, and so on. For a true "reset" function you should first delete the existing formatconditions and then add them back via code.

[mattadams84]

Thanks v much. I have got it working now. Thank you