When is a FormatCondition not a FormatCondition?

I was recently reminded of this quirk of the Excel object model (you guessed it – by a forum post). Suppose you have this code:

Dim fc As FormatCondition
For Each fc In ActiveSheet.UsedRange.FormatConditions
   ' do something to fc here
Next fc

you’d probably be slightly surprised if you received a Type Mismatch error on the second line. After all, you’re using a FormatCondition control variable to loop through the FormatConditions collection – what could be simpler?

Well, as it happens, the FormatConditions collection isn’t restricted to only FormatCondition objects. Yes, I know. It’s not my fault, so don’t shout at me.

It can actually contain all the following object types, which relate to the various fancy conditional formatting options available since Excel 2007 (shudder):

FormatCondition
DataBar
AboveAverage
ColorScale
UniqueValues
Top10
IconSetCondition

so in the original code, you actually need:
Dim fc as Object
and you can then test the object types using TypeName or TypeOf according to your needs/preference.

2 thoughts on “When is a FormatCondition not a FormatCondition?

Leave a Reply

Your email address will not be published. Required fields are marked *