Sleeper: Checking for ColorIndex following Conditional Formatting

[Shellgrip]

A friend (really!) asked me the other day if there was a way that cells could be filled with values dependent upon their fill colour, the result of simple conditional formatting. I said, sure, easy, of course.

The problem I'm seeing is that querying the value of ColorIndex on these cells that have been conditionally formatted (CF) always returns -4142 (which, I think, is the standard reply for an 'unfilled' cell. If the cells are filled 'manually' using the paint bucket - with the same colours - the code works fine.

We've checked, checked and triple checked. CF is definitely using the same colours as the manual filling. There's no weird palettes in use. The manually filled cells work fine so the code isn't at fault.

The best explanation I can offer is that it's as though the cells aren't really filled because it's the result of CF - as though the colour isn't really there.

Any logical explanation for this? http://vbaexpress.com/forum/images/smilies/119.gif

Jon
(Excel 2003 on XP Pro)

[Bob Phillips]

You are correct in deducing that CF colour is not the same as interior colour, it seems to sit 'above' a cell's interior colour.

To determnine a cell's CF c olour is surprisingly complex. This function returns whether a cell CF colour COULD be a certain colour, but not necessarily is



Function IsCFColour(rng As Range, ci As Long) As Boolean
Dim cCFs As Long
cCFs = rng.FormatConditions.Count
If cCFs = 1 Then
IsCFColour = rng.FormatConditions(1).Interior.ColorIndex = ci
ElseIf cCFs = 2 Then
IsCFColour = rng.FormatConditions(1).Interior.ColorIndex = ci Or _
rng.FormatConditions(2).Interior.ColorIndex = ci
ElseIf cCFs = 3 Then
IsCFColour = rng.FormatConditions(1).Interior.ColorIndex = ci Or _
rng.FormatConditions(2).Interior.ColorIndex = ci Or _
rng.FormatConditions(3).Interior.ColorIndex = ci
End If
End Function

To determine whether it is requires testing whether the formatcondition is actually met, which means testing for value or formula, and every possible type of value, equals, not equals etc.

.

[Shellgrip]

Thanks XLD. I've actually made him swerve around the problem by simply coding the conditional formatting aspect as well. It's nice to know I wasn't going mad - well, not for that reason anyway.

Jon

[mdmackillop]

Hi Shellgrip,
Have a look at this item
Regards
MD
http://vbaexpress.com/kb/getarticle.php?kb_id=190

[Shellgrip]

Blimey, now I definitely think it's easier to program the conditional formatting in the first place :bug:

[Bob Phillips]

Blimey, now I definitely think it's easier to program the conditional formatting in the first place :bug:

To quote me

To determnine a cell's CF colour is surprisingly complex.