Conditional Format Color

[jstevens]

How does one obtain the color index of a conditional format with VBA?

I don't believe it is the same as formatting a cell's interior color index. Example: Range("A1").Interior.ColorIndex = 19

[HansV]

If you mean the color index the cell currently has, you can use the DisplayFormat property:

Code: Select all

Debug.Print Range("A1").DisplayFormat.Interior.ColorIndex

[jstevens]

Hans,

I was able to obtain the correct DisplayFormat.Interior.ColorIndex with this bit of code and would like to convert it to a function

Code: Select all

Sub GetCFInteriorColorIndex()

Dim rng As Range

For Each rng In Range("tblMyTable[Status]")
    Debug.Print rng & "   " & rng.DisplayFormat.Interior.ColorIndex
Next rng

End Sub

Function Code =CFInteriorColor(tblMyTable[Status])

Code: Select all

Function CFInteriorColor(InRange As Range)

Dim rng As Range

Application.Volatile True
For Each rng In InRange.Cells
    Debug.Print rng & "   " & rng.DisplayFormat.Interior.ColorIndex   'Does not work do to colorindex
Next rng

End Function

Any suggestions would be appreciated. I'm going to add code to the Function once the ColorIndex works.

[HansV]

As you have found, DisplayFormat does not work in a UDF (User-Defined Function). This is documented by Microsoft - see Range.DisplayFormat property (Excel).

Fortunately, there is a workaround - see Using DisplayFormat in a UDF. Let me know if you have trouble modifying that for your purpose.

[jstevens]

Hans,

Thank you for the workaround suggestion. I got it to work.