13

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?

Community
  • 1
  • 1

7 Answers7

10

.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!"
richardtallent
  • 34,724
  • 14
  • 83
  • 123
  • can you show me how to get the conditionally-formatted color? –  Jun 15 '09 at 14:53
  • 1
    how can i increase the importance of your answer? –  Jun 15 '09 at 15:25
  • Click on the "up" button above the number "0" to the left of my answer, or if it's the answer you want to accept, click the outlined "check" that appears on the left of my answer. – richardtallent Jun 15 '09 at 15:30
  • the problem is that then it asks me the openid which i copy and paste and it does not accept –  Jun 15 '09 at 15:36
  • 1
    You need 15+ reputation to vote up, and only have 11 currently. You should upvote all the questions that were helpful, and click the checkmark for the best answer. – Andrew Scagnelli Jun 15 '09 at 15:44
6

You need to refer the <Cell>.FormatConditions(index that is active).Interior.ColorIndex to retrieve the conditional formatting color of a cell.

You may refer to the below link for an example:

http://www.xldynamic.com/source/xld.CFConditions.html#specific

Benoit Garret
  • 14,027
  • 4
  • 59
  • 64
Ankita
  • 61
  • 1
  • 1
  • +1, but do note that `.FormatConditions(index that is active)` returns an object that will need to be cast to type `FormatCondition`. – Peter Majeed Jul 16 '12 at 17:14
3

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

SengMing
  • 143
  • 6
0

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!

0

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
jitter
  • 53,475
  • 11
  • 111
  • 124
  • 1
    I am only interested in the Color or ColorIndex of the cell(which is colored using conditional formatting) only... anyways thanks –  Jun 15 '09 at 14:52
0

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.

Andrew Scagnelli
  • 1,584
  • 4
  • 18
  • 26
  • MsgBox Range("A1", "C3").Cells(1, 1).Interior.Color MsgBox Range("A1", "C3").Cells(1, 1).Interior.ColorIndex These two still return the same 16777215 and -4142, respectively –  Jun 15 '09 at 14:42
  • MsgBox Range("A1", "C3").Cells(1, 1).Interior.PatternColorIndex this returns -4142 –  Jun 15 '09 at 14:43
  • Have you tried changing the background color manually via code? – Andrew Scagnelli Jun 15 '09 at 14:44
  • Not conditional formatting -- setting the color of the cell manually. As a reference, green is 5287936, yellow is 65535, and red is 255, – Andrew Scagnelli Jun 15 '09 at 14:57
  • if manually then i also know how to get the color info... i have problems getting the color of cell which is conditionally formatted –  Jun 15 '09 at 15:03
  • The conditional formatting could be overwriting or overriding any changes you make; in this case, you might have to do conditional formatting via VBA instead of with Excel's builtin functionality. – Andrew Scagnelli Jun 15 '09 at 15:22
0

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?

SqlRyan
  • 33,116
  • 33
  • 114
  • 199