mattadams84
Board Regular
- Joined
- Oct 30, 2016
- Messages
- 54
Hi,
I am trying to apply conditional formatting using VBA. I have got this code to work successfully :
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 :
The problem i am getting is on this line :
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...
I am trying to apply conditional formatting using VBA. I have got this code to work successfully :
Code:
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 :
Code:
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 :
Code:
.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...
Last edited: