How to find the fill color value of a conditionally formatted cell in Excel 2007 using vba?

Question

I use the color bar for my conditional formatting in Excel 2007, and it's hard for me to find fill code for conditionally formatted cells. I know that Interior.Color returns the default color value, but this does not help when using conditional formatting. I am very surprised how difficult it is to do it.

Thanks.

Answer 1

You can access the internal color of the fomatting conditions (and not what currently belongs to the cell), assuming that this is the first condition applied to the cell:

Range("A1").FormatConditions(1).interior.color 

Here is a function that will return color codes for all conditional formats that the cell contains. It will not return anything if there are no conditions, and if there is a condition, but color is not set for it, then it tells you "none".

 Function ConditionalColor(ByVal cell As Range) Dim colors As String Dim i As Long For i = 1 To Range(cell.Address).FormatConditions.count If Range(cell.Address).FormatConditions(i).Interior.Color <> 0 Then colors = colors & "Condition " & i & ": " & _ Range(cell.Address).FormatConditions(i).Interior.Color & vbLf Else colors = colors & "Condition " & i & ": None" & vbLf End If Next If Len(colors) <> 0 Then colors = Left(colors, Len(colors) - 1) End If ConditionalColor = colors End Function 

UPDATE : If you're interested (I was), the color code that Excel uses is actually BGR, not RGB. Therefore, if you want to convert the code to RGB values, you can use this:

 Function GetRGB(ByVal cell As range) As String Dim R As String, G As String Dim B As String, hexColor As String hexCode = Hex(cell.Interior.Color) 'Note the order excel uses for hex is BGR. B = Val("&H" & Mid(hexCode, 1, 2)) G = Val("&H" & Mid(hexCode, 3, 2)) R = Val("&H" & Mid(hexCode, 5, 2)) GetRGB = R & ":" & G & ":" & B End Function 

Answer 2

Hey. The answers you provided did not work because I use the color scale so that it does not return the normal values ​​of 3 conditions.

After much more searching, I found one workaround that works. That is, to take the data and put it in a word, then copy it back to excel so that the range changes to the true color in the cell, so that Interior.Color will work. I found the one who took it and put it in VBA. Here is the link if anyone else wants to do this.

Answer 3

The code below was taken from VBAExpress, all of them also belong to the original author - byundt.

May need to change for excel 2007.

Original link

 Function ConditionalColor(rg As Range, FormatType As String) As Long 'Returns the color index (either font or interior) of the first cell in range rg. If no _ conditional format conditions apply, Then returns the regular color of the cell. _ FormatType Is either "Font" Or "Interior" Dim cel As Range Dim tmp As Variant Dim boo As Boolean Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String Dim i As Long 'Application.Volatile 'This statement required if Conditional Formatting for rg is determined by the _ value of other cells Set cel = rg.Cells(1, 1) Select Case Left(LCase(FormatType), 1) Case "f" 'Font color ConditionalColor = cel.Font.ColorIndex Case Else 'Interior or highlight color ConditionalColor = cel.Interior.ColorIndex End Select If cel.FormatConditions.Count > 0 Then 'On Error Resume Next With cel.FormatConditions For i = 1 To .Count 'Loop through the three possible format conditions for each cell frmla = .Item(i).Formula1 If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True 'Conditional Formatting is interpreted relative to the active cell. _ This cause the wrong results If the formula isn 't restated relative to the cell containing the _ Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _ If the Function were Not called using a worksheet formula, you could just activate the cell instead. frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell) frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel) boo = Application.Evaluate(frmlaA1) Else 'If "Value Is", then identify the type of comparison operator and build comparison formula Select Case .Item(i).Operator Case xlEqual ' = x frmla = cel & "=" & .Item(i).Formula1 Case xlNotEqual ' <> x frmla = cel & "<>" & .Item(i).Formula1 Case xlBetween 'x <= cel <= y frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")" Case xlNotBetween 'x > cel or cel > y frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")" Case xlLess ' < x frmla = cel & "<" & .Item(i).Formula1 Case xlLessEqual ' <= x frmla = cel & "<=" & .Item(i).Formula1 Case xlGreater ' > x frmla = cel & ">" & .Item(i).Formula1 Case xlGreaterEqual ' >= x frmla = cel & ">=" & .Item(i).Formula1 End Select boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula End If If boo Then 'If this Format Condition is satisfied On Error Resume Next Select Case Left(LCase(FormatType), 1) Case "f" 'Font color tmp = .Item(i).Font.ColorIndex Case Else 'Interior or highlight color tmp = .Item(i).Interior.ColorIndex End Select If Err = 0 Then ConditionalColor = tmp Err.Clear On Error GoTo 0 Exit For 'Since Format Condition is satisfied, exit the inner loop End If Next i End With End If End Function 

Answer 4

I do not have an answer that works with Excel 2007 or lower, but from Excel 2010 onwards you can use the following (changing the range):

 Range("A1").DisplayFormat.Interior.ColorIndex 

Fortunately, while the software for which I need it is supported in Excel 2003, I actually only require it in the test procedure, and the test module is removed from production versions.

Answer 5

Easy way: Print a spreadsheet. Paste it in the paint. Use the eyedropper tool to find the color. Click Change Color.

BOOM has detected your RGB information that you can enter back into excel