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

user_6237093 0
Viewed 51614

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.

vba excel
4 Answers

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
user_34681390
Related