How to get the background color from a Conditional Formatting in Excel using VBA

Question

I would like to obtain the cell background color assigned using a conditional formatting rule in Excel in my VBA script. I realized that using Range.Interior.Color property doesn't have the color resulting of an applied conditional formatting feature from Excel.

I did some research and I found this long way here, it compiles and runs but I don't get the assigned color [I get always (255,255,255)]

I am using Excel 2016 and I am wondering if there is a simpler way to obtain this information using some built-in VBA function or using any other excel trick.

Answer 1

If you want to know the color of a cell that has been colored by a conditional formatting rule (CFR) then use Range.DisplayFormat.Interior.Color¹.

If you want to definitively know what color a cell may or may not have been colored by a CFR you need to iterate through the CFRs that could be affecting that cell and look at each of the Range.FormatConditions(*x*).Interior.Color.

Documentation: range.DisplayFormat

¹ _{Note: .DisplayFormat is not available for a worksheet UDF.}

Answer 2

The code below gives the HEX and RGB value of the range whether formatted using conditional formatting or otherwise. If the range is not formatted using Conditional Formatting and you intend to use iColor function in the Excel as UDF. It won't work. Read the below excerpt from MSDN.

Note that the DisplayFormat property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, if you use a line similar to:

Range.DisplayFormat.Interior.ColorIndex

then the worksheet function executes to return a #VALUE! error.

Public Function iColor(rng As Range, Optional formatType As String) As Variant
'formatType: Hex for #RRGGBB, RGB for (R, G, B) and IDX for VBA Color Index
    Dim colorVal As Variant
    colorVal = rng.DisplayFormat.Interior.Color
    Select Case UCase(formatType)
        Case "HEX"
            iColor = "#" & Format(Hex(colorVal Mod 256),"00") & _
                           Format(Hex((colorVal \ 256) Mod 256),"00") & _
                           Format(Hex((colorVal \ 65536)),"00")
        Case "RGB"
            iColor = Format((colorVal Mod 256),"00") & ", " & _
                     Format(((colorVal \ 256) Mod 256),"00") & ", " & _
                     Format((colorVal \ 65536),"00")
        Case "IDX"
            iColor = rng.Interior.ColorIndex
        Case Else
            iColor = colorVal
    End Select
End Function

'Example use of the iColor function
Sub Get_Color_Format()
    Dim rng As Range

    For Each rng In Selection.Cells
        rng.Offset(0, 1).Value = iColor(rng, "HEX")
        rng.Offset(0, 2).Value = iColor(rng, "RGB")
    Next
End Sub

Answer 3

You want Range.DisplayFormat if you need to account for Conditional Formatting

(added in Excel 2010)

Answer 4

.FormatCondition property of range or selection should help with any formatting Use With...End With

If you want to know the exact color RGB values, u can just try recording macro and get the rgb values.