UDF not updating

[mmenofy]

Hello,

I've the following UDF but it's not auto updating

Function GetFillColor(Rng As Range) As Long
GetFillColor = Rng.Interior.ColorIndex
End Function

I tried (Application.Volatile) and most of the solution found on internet but nothing changed, Any way to solve it??

 

[Skyybot]

Show me an example of how you are calling it.

 

[Skyybot]

Seems to be working for me.

Function GetFillColor(Rng As Range) As Long
GetFillColor = Rng.Interior.ColorIndex
End Function

Sub FillColor()
MsgBox "The fill color of the Selected cell is " & GetFillColor(Selection) & ".", vbOKOnly, "Fill Color"
End Sub

 

[mmenofy]

Show me an example of how you are calling it.

in the following equation (The following is just one cell)

=IF(I13="","",GetFillColor(H13))

 

[Skyybot]

It seems to work for me. I don't think that you can use a Formula to change cell Format. Maybe use Conditional Formatting instead?

Book1
	A	B
1	G	6
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(A1="","",GetFillColor(A1))

 

[Alex Blakenburg]

Is your colour being changed using Conditional Formatting by any chance ?
If it is try the solution provided here by @Jaafar Tribak

Using DisplayFormat in a UDF

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...

www.mrexcel.com

PS: Just change Color to ColorIndex

 

[mmenofy]

the formula is only to get the color code (to be used in other cells), i just have legend of colors and want to determine the color code of the cell to be used in other thread

 

[RoryA]

Changing a cell's fill colour does not trigger a recalculation, even if your function is volatile. IMO any attempt to use colours as actual data is flawed.

 

[mmenofy]

I noticed that changing colors aren't considered as change in the workbook
I added a button to force calculate the sheet and it worked for me

Thank you so much