How do I find the fill colour value of a conditionally formatted cell in Excel 2007 using vba?

Question

I am using a colour scale for my conditional formatting in Excel 2007 and I am having a hard time finding out the fill colour code for the conditionally formatted cells. I know Interior.Color returns the default colour value but that does not help when using conditional formatting. I am relay quite surprised at how hard this has been to do.

Thank you.

Answer 1

You can access the interior color of the fomatting conditions (not what the cell currently is) like so, assuming there this is the first condition applied on the cell:

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

Here's a function that will return the color codes for all the conditional formats a cell contains. It will return nothing if there are no conditions, and if there is a condition but no color is 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: In case you are curious (I was), the color code that Excel uses is actually BGR, not RGB. So if you wanted 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

Hi The answers you have provided did not work because I am using a color scale so it does not return the normal 3 condition values.

After much more searching I found one workaround that works. That is to take the data and put it into word then copy it back into excel making the range go to a true colour in the cell so Interior.Color will work. I found someone that has taken and put it into VBA. Here is the link to it if anyone else is looking to do this.