Conditional Formatting and Interior.ColorIndex

[Farrar > Tweety]

Windows XP Pro SP2
Excel 2002 SP3

Seems that cells that are conditionally formatted don't return an expected
Interior.ColorIndex value.

I am using this bit of code:

Set rng1 = Range("B2020")
For Each c1 In rng1.Cells
v1 = c1.Value
If v1 <> "" Then
Set rng2 = Range(c1.Offset(3, 0), c1.Offset(32770, 0))
rng2.FormatConditions.Add xlCellValue, xlEqual, v1
rng2.FormatConditions(1).Interior.ColorIndex = 4
rng2.FormatConditions.Add xlCellValue, xlNotEqual, v1
rng2.FormatConditions(2).Interior.ColorIndex = 46
End If
Next c1

This works fine and colors the interiors as I would expect. However, if I
add this after the last line above

MsgBox Range("G23").Interior.ColorIndex 'G23 is colored red (46)

the message box says -4142.

What's up with that?

Any help greatly appreciated.

-gk-

GO TAR HEELS ! ! !

 

[Mike H]

Hi,

Excel will retirn -4142 if the interior has no colour and you can't get the
index of a cell if it is coloured by conditional formatting. The most common
approach is to test for the condition that would cause the cell to change
colour.

Mike

 

[SteveDB1]

Mike,
I'm using a colorIndex function for a search routine to identify when a cell
or range of cells is NOT colored.
I'd like to do a search to locate a colored cell, but since I never know
what the color will be, I thought having it look for its antithesis might be
better.
Got any thoughts/ideas?
Thanks.