I was recently reminded of this quirk of the Excel object model (you guessed it – by a forum post). Suppose you have this code:
For Each fc In ActiveSheet.UsedRange.FormatConditions
' do something to fc here
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):
so in the original code, you actually need:
Dim fc as Object
and you can then test the object types using
TypeOf according to your needs/preference.