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