Is there a limit to the number of conditional formatting rules?

[288enzo]

Everything was working fine until I added my 11th rule.

Now, on my 11th rule I'm getting Subscript out of range, Run-time error '9':

When I run this by itself, I have no troubles.

Sub anothertest()
    With Range(Cells(2, 14), Cells(574, 14))
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT(RC14,5)=""To Go"""
        With .FormatConditions(1)
            .Interior.Color = RGB(255, 192, 0) 'ORANGE
            .StopIfTrue = False
        End With
    End With
End Sub

When I incorporate it into the following, that's when I get the out of range error.

    With Cells
   
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlTextString, String:="N/A", TextOperator:=xlContains
        With .FormatConditions(1)
            .Interior.Color = RGB(0, 0, 0) 'BLACK
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="Complete", TextOperator:=xlBeginsWith
        With .FormatConditions(2)
            .Interior.Color = RGB(0, 176, 80) 'GREEN
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="Registered", TextOperator:=xlContains
        With .FormatConditions(3)
            .Interior.Color = RGB(255, 255, 0) 'YELLOW
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="Needs*", TextOperator:=xlContains
        With .FormatConditions(4)
            .Interior.Color = RGB(255, 0, 0) 'RED
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="*Remaining", TextOperator:=xlContains
        With .FormatConditions(5)
            .Interior.Color = RGB(255, 0, 0) 'RED
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="Required", TextOperator:=xlContains
        With .FormatConditions(6)
            .Interior.Color = RGB(255, 0, 0) 'RED
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="Test", TextOperator:=xlContains
        With .FormatConditions(7)
            .Interior.Color = RGB(112, 48, 160) 'PURPLE
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="Up to Date", TextOperator:=xlContains
        With .FormatConditions(8)
            .Interior.Color = RGB(0, 176, 80) 'GREEN
            .StopIfTrue = False
        End With

    End With
   
    With Range(Cells(2, 18), Cells(LR, 18))
        .FormatConditions.Add Type:=xlTextString, String:="Yes", TextOperator:=xlContains
        With .FormatConditions(9)
            .Interior.Color = RGB(0, 176, 80) 'GREEN
            .StopIfTrue = False
        End With
        .FormatConditions.Add Type:=xlTextString, String:="No", TextOperator:=xlContains
        With .FormatConditions(10)
            .Interior.Color = RGB(255, 0, 0) 'RED
            .StopIfTrue = False
        End With
    End With
   
    With Range(Cells(2, 14), Cells(LR, 14))
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT(RC14,5)=""To Go"""
        With .FormatConditions(11)
            .Interior.Color = RGB(255, 192, 0) 'ORANGE
            .StopIfTrue = False
        End With
    End With

 

[rlv01]

You are getting that error because you are trying to reference a format condition that does not exist. A format condition collection is a range property. You have to start at 1 again when you add a new format condition for a new range w/o any previously defined format conditions, and With Range(Cells(2, 18), Cells(LR, 18)) is a new range.

 

[rlv01]

You are better off using a variable when adding a format condition. That way you don't need to keep track of the collection index numbers when adding multiple format conditions.

Dim FC As FormatCondition

    With Cells
        .FormatConditions.Delete
        
        Set FC = .FormatConditions.Add(Type:=xlTextString, String:="N/A", TextOperator:=xlContains)
        With FC
            .Interior.Color = RGB(0, 0, 0)            'BLACK
            .StopIfTrue = False
        End With
        Set FC = Nothing

 

[Fluff]

The simplest way (IMO) is to do it like

    With Cells
   
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlTextString, String:="N/A", TextOperator:=xlContains
        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(0, 0, 0) 'BLACK
            .StopIfTrue = False
        End With

 

[rlv01]

Or that.

 

[288enzo]

The simplest way (IMO) is to do it like

    With Cells
  
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlTextString, String:="N/A", TextOperator:=xlContains
        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(0, 0, 0) 'BLACK
            .StopIfTrue = False
[QUOTE="rlv01, post: 5930103, member: 403997"]
You are better off using a variable when adding a format condition. That way you don't need to keep track of the collection index numbers when adding multiple format conditions.

[CODE=vba]Dim FC As FormatCondition

    With Cells
        .FormatConditions.Delete
       
        Set FC = .FormatConditions.Add(Type:=xlTextString, String:="N/A", TextOperator:=xlContains)
        With FC
            .Interior.Color = RGB(0, 0, 0)            'BLACK
            .StopIfTrue = False
        End With
        Set FC = Nothing

End With

[/CODE]
[/QUOTE]
Thank you both, I ultimately chose Fluff's answer as it was the simplest

On a side note, I did try to change the last conditional formatting "To Go" to a 1 because it was a new range, but that didn't work. It basiclly changed all "N/A" cells to orange, which "N/A" was also number 1.

 

[Fluff]

Glad we could help & thanks for the feedback.

I did try to change the last conditional formatting "To Go" to a 1

On this occasion you would need to make it 9 as there are already 8 rules for those cells.