Using DisplayFormat in a UDF

[Jaafar Tribak]

Hi,

I came accross this topic Excel VBA User Defined Function that counts cells with conditional formatting
for counting cells with CF and there was an answer offering a neat workaround which uses the handy Evaluate function to make the DisplayFormat work inside a UDF.

Here is an adaption:

Option Explicit

Function DFColor(ByVal R As Range) As Double
    Application.Volatile
    DFColor = Evaluate("Helper(" & R.Address() & ")")
End Function


Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

Usage:

=DFColor(A1)

I hope you find this useful.