Get cell color of conditionally formatted cell

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

Below is a UDF that will give you cell fill color <b>(including the fill color from Conditional Formatting)</b> without that <b><i>#VALUE error</i></b> 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 <b>Jaafar Tribak</b>'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.

Return Conditional Formatting Colors With a UDF

• The following workaround is based on the first answer to this question by Nay Lynn. Thanks for sharing.

<!-- language: lang-vb -->

Function GetColorCf(ByVal RangeCF As Range) As Variant
    
    Application.Volatile
    
    Dim ws As Worksheet: Set ws = RangeCF.Worksheet
        
    Dim rCount As Long: rCount = RangeCF.Rows.Count
    Dim cCount As Long: cCount = RangeCF.Columns.Count
    
    Dim Data(): ReDim Data(1 To rCount, 1 To cCount)
    
    Dim r As Long, c As Long, ColorValue As Long
    
    For r = 1 To rCount
        For c = 1 To cCount
            Data(r, c) = ws.Evaluate("GetCellColorCfVba(" _
                & RangeCF.Cells(r, c).Address & ")")
        Next c
    Next r
    
    GetColorCf = Data

End Function

Private Function GetCellColorCfVba(ByVal cell As Range) As Long
    With cell.Cells(1) ' ensure single cell
        Dim ColorValue As Long: ColorValue = .DisplayFormat.Interior.Color
        If ColorValue = 16777215 Then
            If .DisplayFormat.Interior.ColorIndex = xlNone Then
                ColorValue = 0
            End If
        End If
    End With
    GetCellColorCfVba = ColorValue
End Function