VBA Conditional Format Error 9

mattadams84

New Member
Joined
Oct 30, 2016
Messages
36
Hi,

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:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Are you sure you already have 5 conditional formatting rules applied to column H?

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

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,587
Members
412,327
Latest member
KLichter
Top