Retrieve Conditional Formatting Color with an Excel Function

Retrieving the color of a conditional formatting using a function in an Excel sheet is not possible in principle, but there is a workaround...

The function detailed here will allow you to retrieve the color of a cell using a simple formula =COLOR(A1).

The COLOR Function

To use the COLOR function in a sheet, simply copy and paste this code into a module:

Function COLOR(cell As Range)

    'Source: https://www.excel-pratique.com/en/vba_tricks/conditional-formatting-color-excel
    
    Application.Volatile
    
    COLOR = Evaluate("cellColor('" & cell.Worksheet.Name & "'!" & cell.Address & ")")
    
End Function

Private Function cellColor(cell As Range)

    cellColor = cell.DisplayFormat.Interior.Color
    
End Function

Another version is also available:

Function COLOR(cell As Range)

    'Source: https://www.excel-pratique.com/en/vba_tricks/conditional-formatting-color-excel
    
    Application.Volatile
    
    COLOR = Evaluate("cellColor(""" & cell.Address & """,""" & cell.Worksheet.Name & """)")
    
End Function

Private Function cellColor(cell As String, sheet As String)

    cellColor = Sheets(sheet).Range(cell).DisplayFormat.Interior.Color
    
End Function