CODEWITHSUNDEEP
excelvbaformattingconditional-statements
ProgrammingPikachu
ProgrammingPikachu

Reputation: 79

Excel VBA Can't create more than 3 conditional formatting rules?

I trying to use to VBA to create a few conditional formatting rules but I keep getting "Subscript out of range" error for the 3rd rule.

This is my current code

Dim MyRange As Range
Dim MyRange2 As Range
Dim MyRange3 As Range

'Define the range for the respective rules
Set MyRange = Range("M4:N30")
Set MyRange2 = Range("A4:Z30")
Set MyRange3 = Range("O4:P30")

MyRange2.FormatConditions.Delete

'Rule 1
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$M4<>""NA""", Formula2:="=$N4<>""NA"""
MyRange.FormatConditions(1).Interior.Color = RGB(255, 150, 0)

'Rule 2
MyRange2.FormatConditions.Add Type:=xlExpression, Formula1:="=$O4>56", Formula2:="=$P4>56"
MyRange2.FormatConditions(2).Interior.Color = RGB(255, 255, 0)

'Rule 3
MyRange3.FormatConditions.Add Type:=xlExpression, Formula1:="=$O4<>$P4"
MyRange3.FormatConditions(3).Interior.Color = RGB(255, 255, 0)

If I change the "3" in FormatConditions(3) to 1 or 2, i dont get errors. Any advise on whats causing the error?

Thank you for your time

Upvotes: 1

Views: 251

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You should be using:

MyRange3.FormatConditions(2).Interior.Color = RGB(255, 255, 0)

because for the range specified i.e. O4:P30 it is second rule and it gives you error because rule number 3 does not exist!

Upvotes: 1

Related Questions