Get cell color of conditionally formatted cell

Question

I'm trying to get the cell color using the VBA script

Public Function GetCellColor(cell As Range) As Long
    Application.Volatile
    GetCellColor = cell.Interior.Color
End Function

This works for manually formatted cells. But if I use a conditionally formatted cell it just shows the value of the cell with no format.

So When I try to use this VBA below which supposed to find conditionally formatted cells. But it gives me a "#VALUE!" error. What am I doing wrong, on the script below?

Function GetCellColor(cell As Range) As String
    Application.Volatile
    GetCellColor = cell.DisplayFormat.Interior.Color
End Function

Answer 1

Below is a UDF that will give you cell fill color (including the fill color from Conditional Formatting) without that #VALUE error that you mentioned.

Option Explicit

Public Function CellFillColor(target As Range, Optional returnFormat As String = "IDX") As Variant
Dim retArray()
Dim rowCounter As Long
Dim colCounter As Long
Dim colorValue As Long
'    Application.Volatile
    If TypeName(target) = "Range" Then
        ReDim retArray(target.Rows.Count - 1, target.Columns.Count - 1)
        For rowCounter = 0 To target.Rows.Count - 1
            For colCounter = 0 To target.Columns.Count - 1
                colorValue = Evaluate("useDF(" & target.Cells(rowCounter + 1, colCounter + 1).Address & ")")
                Select Case UCase(returnFormat)
                    Case "RGB":
                                retArray(rowCounter, colCounter) = _
                                                                    Format((colorValue Mod 256), "00") & ", " & _
                                                                    Format(((colorValue \ 256) Mod 256), "00") & ", " & _
                                                                    Format((colorValue \ 65536), "00")
                    Case "HEX":
                                retArray(rowCounter, colCounter) = _
                                                                    "#" & _
                                                                    Format(Hex(colorValue Mod 256), "00") & _
                                                                    Format(Hex((colorValue \ 256) Mod 256), "00") & _
                                                                    Format(Hex((colorValue \ 65536)), "00")
                    Case "IDX": retArray(rowCounter, colCounter) = colorValue
                    Case Else: retArray(rowCounter, colCounter) = colorValue
                End Select
            Next colCounter
        Next rowCounter
        CellFillColor = retArray 'IIf(target.CountLarge = 1, retArray(0, 0), retArray)
    End If
End Function

Private Function useDF(ByVal target As Range) As Variant
    useDF = target.DisplayFormat.Interior.Color
End Function

'in Immediate Window
'Range("G16").Interior.Color=13551615<-IDX value

It can also be found on my GitHub.

It was based on Jaafar Tribak's code that he shared on mrexcel.

I hope this helps.
Feel free to take it apart and/or rearrange it as above code was written for giving more choices to the user thereby making it (unnecessarily?) longer.