VBA to apply conditional format

Hi all,

I am applying several conditional formats to a sheet with macros. All of them are set up successfully but the one below:

With ThisWorkbook.Sheets("Test").Range("$D$13:$D$1500,$S$13:$T$1500,$V$13:$AB$1500,$AT$13:$BE$1500,$CI$13:$CK$1500,$CO$13:$CQ$1500,$DA$13:$EC$1500,$FN$13:$FQ$1500,$FU$13:$FW$1500,$GG$13:$HI$1500,$IT$13:$IW$1500,$JA$13:$JC$1500,$JM$13:$KO$1500,$LZ$13:$MC$1500,$MG$13:$MI$1500,$MS$13:$NU$1500,$PF$13:$PI$1500,$PM$13:$PO$1500,$PY$13:$RA$1500,$SL$13:$SO$1500,$SS$13:$SU$1500,$TE$13:$UG$1500,$VR$13:$VU$1500,$VY$13:$WA$1500,$O$13:$P$1500")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($D13=""DROP"",$E13=""INACTIVE"")"
        With .FormatConditions(.FormatConditions.Count)
             .SetFirstPriority
             .StopIfTrue = True
            With .Interior
                 .Color = RGB(178, 188, 198)
            End With
                With .Font
                     .Bold = False
                     .Italic = False
                     .Color = RGB(132, 146, 160)
                End With
        End With
    End With

I get a run-time error 1004. I assume there's too many ranges referenced? I tried Union(Range( as well, but no luck...

Try reducing the number of ranges and see if you get the error.

I tried before and it worked! Hence, I assume the number of ranges causes the error...

I found on another forum that the number of ranges maximum is 16. (EDIT - may not be accurate on second look).

And recommend using UNION to join the ranges.

Originally Posted by kersplash

I found on another forum that the number of ranges maximum is 16. (EDIT - may not be accurate on second look).

And recommend using UNION to join the ranges.

Would using Union increase such a potential maximum?

Is it possible there is a syntax error in one of the ranges?

The address that you pass to range can't be longer than 255 characters - yours is 392. Even if you remove the unnecessary $ signs you are left with 292, so you do need to union 2 ranges, or apply the CF in two blocks.

How would such a Union Range look like? I tried:

With ThisWorkbook.Sheets("Test").Union(Range("$D$13:$D$1500,$S$13:$T$1500,$V$13:$AB$1500,$AT$13:$BE$1500,$CI$13:$CK$1500,$CO$13:$CQ$1500,$DA$13:$EC$1500,$FN$13:$FQ$1500,$FU$13:$FW$1500,$GG$13:$HI$1500,$IT$13:$IW$1500,$JA$13:$JC$1500,$JM$13:$KO$1500"), Range("$LZ$13:$MC$1500,$MG$13:$MI$1500,$MS$13:$NU$1500,$PF$13:$PI$1500,$PM$13:$PO$1500,$PY$13:$RA$1500,$SL$13:$SO$1500,$SS$13:$SU$1500,$TE$13:$UG$1500,$VR$13:$VU$1500,$VY$13:$WA$1500,$O$13:$P$1500"))
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($D13=""DROP"",$E13=""INACTIVE"")"
        With .FormatConditions(.FormatConditions.Count)
             .SetFirstPriority
             .StopIfTrue = True
            With .Interior
                 .Color = RGB(178, 188, 198)
            End With
                With .Font
                     .Bold = False
                     .Italic = False
                     .Color = RGB(132, 146, 160)
                End With
        End With
    End With

But it gets me a "Object doesn't support this property or method" error...

Union is not a worksheet property. You should use:

With Union(ThisWorkbook.Sheets("Test").Range("D13:D1500,S13:T1500,V13:AB1500,AT13:BE1500,CI13:CK1500,CO13:CQ1500,DA13:EC1500,FN13:FQ1500,FU13:FW1500,GG13:HI1500,IT13:IW1500,JA13:JC1500,JM13:KO1500"), ThisWorkbook.Sheets("Test").Range("LZ13:MC1500,MG13:MI1500,MS13:NU1500,PF13:PI1500,PM13:PO1500,PY13:RA1500,SL13:SO1500,SS13:SU1500,TE13:UG1500,VR$13:VU1500,VY13:WA1500,O13:P1500"))

Thank you!! That works