Excel 2007 conditional formatting - how to get cell color?

Question

Let's assume i have the following range from (a1:c3)

  A B C
1 -1 1 1
2 -1 0 0
3  0 0 1

Now i have selected the following range, and formatted it using Conditional Formatting (using default red yellow green color scale).... now range colors became

    A         B         C
1 Green    Red     Red
2 Green   Yellow Yellow
3 Yellow Yellow Red

Now I want to ask the color of any cell in the range, for example MsgBox Range("A1").Interior.Color but it does not say that it is Green, why? Plz can you help me?

Range("A1").Interior.Color always returns 16777215 Range("A1").Interior.ColorIndex always returns -4142

(no matter whether the color of A1 is red, blue, green, ...)

Range("A1", "C3").FormatConditions.Count this one returns always 0, why?

Answer 1

.Interior.Color returns the "real" color, not the conditionally-formatted color result.

@sss: It's not available via the API.

The best you can do is to test the same conditions you used in the conditional formatting.

To avoid this resulting in duplicate code, I suggest moving your conditional criteria to a UDF. Examples:

Function IsGroup1(ByVal testvalue As Variant) As Boolean
   IsGroup1 = (testvalue < 0)
End Function

Function IsGroup2(ByVal testvalue As Variant) As Boolean
   IsGroup1 = (testvalue = 0)
End Function

Function IsGroup3(ByVal testvalue As Variant) As Boolean
   IsGroup1 = (testvalue > 0)
End Function

Then use these formulas in your Conditional formatting:

=IsGroup1(A1)
=IsGroup2(A1)
=IsGroup3(A1)

Then your code, rather than looking at the color of the cells, looks to see if the condition is met:

If IsGroup1(Range("$A$1").Value) Then MsgBox "I'm red!"