Excel formula do not update automatically

0
votes

I have created a simple excel formula to know what is the color of the font of the cell A1

Function GetFontColorIndex(elrango As Range) As Integer
        Application.Volatile
       GetFontColorIndex = elrango.Cells(1, 1).Font.ColorIndex
End Function

In :
File > Options > Formulas > Workbook Calculation > Automatic is checked

In cell A1 I have a number, and in cell A3 I have
=GetFontColorIndex(A1)

but when I change the font of cell A1, the formula does not update automatically on cell A3. I have to click shift+F9 and then it works.

Any idea why is not showing the number of the font automatically?

1
excelvba
From here: A Volatile Function in a formula in a cell makes that cell be always recalculated at each recalculation. Changing formatting (font color) doesn't triggers recalculation. what you can do is add Worksheet_SelectionChange event with ActiveSheet.Calculate. In many cases it would solve your problem – Dmitry Pavliv
You can also use XLM for this. – brettdj

1 Answers

1
votes

As simoco has mentioned.

If you are using your 'udf' in Sheet1 then in the module associated with that sheet add the following:

enter image description here

If you just change the colour the formula does not update but as soon as you press enter or select another cell in the sheet it updates