Excel 2007 conditional formatting - how to get cell color?

Question

Hi,

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

According to XlColorIndex Enumeration ColorIndex=-4142 means No color

As to why this happens I'm clueless. The returned value seems to be the decimal representation of the RGB value. The improved version of this script to decrypt the value into hex RGB notation

Function RGB(CellRef As Variant)
   RGB = ToHex(Range(CellRef).Interior.Color)
End Function

Function ToHex(ByVal N As Long) As String
   strH = ""
   For i = 1 To 6
      d = N Mod 16
      strH = Chr(48 + (d Mod 9) + 16 * (d \ 9)) & strH
      N = N \ 16
   Next i
   strH2 = ""
   strH2 = Right$(strH, 2) & Mid$(strH, 3, 2) & Left$(strH, 2)
   ToHex = strH2
End Function

Answer 2

To get the color of a cell in a Range, you need to reference the individual cell inside the array in the form of Range("A1","C3").Cells(1,1) (for cell A1). The Excel help is pretty good if you look up the name of the property you're having issues with.

Also, Excel 2007 uses Integers for its color types, so your best bet is to assign the color index to an integer, and using that throughout your program. For your example, try:

Green = Range("A1","C3").Cells(1,1).Interior.Color
Yellow = Range("A1","C3").Cells(1,3).Interior.Color
Red = Range("A1","C3").Cells(2,1).Interior.Color

And then to switch the colors to all red:

Range("A1","C3").Interior.Color = Red

Again, check the Excel help for how to use Cells([RowIndex],[ColumnIndex]).

If the above doesn't work for you, check to see what .Interior.PatternColorIndex is equal to. I typically leave it set at xlAutomatic (solid color), and it could be set to something else if the color isn't changing.

Answer 3

.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!"

Answer 4

It doesn't appear that the "Conditional Format"-color is available programmatically. What I'd suggest that, instead, you write a small function that calculates cell color, and then just set a macro to run it on the active cell whenever you've edited the value. For example (sorry for the psuedo-code - I'm not a VBA expert anymore):

Function GetColorForThisCell(Optional WhatCell as String) as Int

   If WhatCell="" Then WhatCell = ActiveCell

   If Range(WhatCell).value = -1 then GetColorForThisCell = vbGreen
   If Range(WhatCell).value =  0 then GetColorForThisCell = vbYellow
   If Range(WhatCell).value =  1 then GetColorForThisCell = vbRed
End Function

Sub JustEditedCell
   ActiveCell.color = GetColorForThisCell()
End Sub

Sub GetColorOfACell(WhatCell as string)
   Msgbox(GetColorForThisCell(WhatCell) )
End Sub

Though you wouldn't be able to use the built-in Excel Conditional Formatting, this would accomplish the same thing, and you'd be able to read the color from code. does this make sense?

Answer 5

As a follow up to @richardtallent (sorry, I couldn't do comments), the following link will get you a function that returns you the color index by evaluating the conditional formatting for you.

http://www.bettersolutions.com/excel/EPX299/LI041931911.htm

Answer 6

since i may have more than three different colors in a time... i didn't find any good way of handling this with conditional formatting's default colors... i did it this way. then whenever i ask the color of the cell, i retrieve the correct color!

 for (int t = 0; t < d_distinct.Length; t++ )
 {                        
   Excel.FormatCondition cond =
    (Excel.FormatCondition)range.FormatConditions.Add(
    Excel.XlFormatConditionType.xlCellValue,
    Excel.XlFormatConditionOperator.xlEqual, 
    "="+d_distinct[t],
    mis, mis, mis, mis, mis);
   cond.Interior.PatternColorIndex = 
    Excel.Constants.xlAutomatic;
  cond.Interior.TintAndShade = 0;
  cond.Interior.Color = ColorTranslator.ToWin32(c[t]);
  cond.StopIfTrue = false;                        
}

d_distinct holds all the distinct values in a range... c is a Color[] which holds distinct colors for every distinct value! this code can easily be translated to vb!