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
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):
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.
Thanks Rory. I found this page by Colin Legg, after I outlined some issues I was having at http://dailydoseofexcel.com/archives/2015/04/25/timing-formulas/
Nice place you’ve got here!
Cheers
Jeff
Thanks, Jeff. Always nice to know someone got some benefit out of my ramblings! 🙂