Get color from conditional formats

Question

I have this function to get the color which is set by a conditional format.

Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.DisplayFormat.Interior.ColorIndex
End Function

But when I use this function in my worksheet it always returns as #value.

How can I make this work?

That is exactly the behaviour described in the documentation: [Range.DisplayFormat property](https://learn.microsoft.com/en-us/office/vba/api/excel.range.displayformat). It says also ***"Note that the DisplayFormat property does not work in user defined functions."*** but you actually tried to use it in a user defined function. So it is a feature not a bug. — Pᴇʜ, Mar 15 '19 at 08:43

score 2 · Answer 1 · answered Mar 15 '19 at 09:11

2

Try this

Dim clrIndex As Integer

Function ColorIndex(CellColor As Range)
    CellColor.Parent.Evaluate "GetColor(" & CellColor.Address(False, False) & ")"
    ColorIndex = clrIndex
End Function

'~~> Get the color index of the cell and store in a temp variable
Sub GetColor(RefCell As Range)
    clrIndex = RefCell.DisplayFormat.Interior.ColorIndex
End Sub

For more explanation you may want to see this amazing Thread by @TimWilliams

answered Mar 15 '19 at 09:11

Siddharth Rout

147,039
17
206
250

Thanks this works for me. But now my function is not updating automaticly when i change a value in my sheet. I added the code "Application.Volatile" on top of your code, but it wont work. – Jelle van der Heijden Mar 15 '19 at 10:01
Add `Application.Volatile` after the line `Function ColorIndex(CellColor As Range)` – Siddharth Rout Mar 15 '19 at 10:09
Yes that works but i want to update all the functions i use, and that F2 method only updates that single function – Jelle van der Heijden Mar 15 '19 at 11:05

score 0 · Answer 2 · answered Mar 15 '19 at 11:16

The solution is this:

Dim clrIndex As Integer

Function ColorIndex(CellColor As Range)
CellColor.Parent.Evaluate "GetColor(" & CellColor.Address(False, False) & ")"
ColorIndex = clrIndex
End Function

'~~> Get the color index of the cell and store in a temp variable
Sub GetColor(RefCell As Range)
clrIndex = RefCell.DisplayFormat.Interior.ColorIndex
End Sub

And for updating the code i use

Application.CalculateFullRebuild

Thanks @Siddharth Rout

Get color from conditional formats

2 Answers2