Search code examples
excelvbacolorsuser-defined-functionsconditional-formatting

How to trigger an update for UDF

I found code to generate a number associated to color cells to use for conditionals

UDFs apparently do not update themselves automatically after changing the cell value, this seems to be a problem with conditionals on color cells.

How can I tweak this code so once I change the color of a cell, the macro will automatically update the associated number?

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

Example spreadsheet:
Upon switching column 'H' from green to white, the associated number in column 'I' should change from 35 to -4142.

Example spreadsheet

Solution

  • Changing the cell's color doesn't fire the change event in the worksheet.

    But if you change at least one cell (for example typing something) after color changes, the UDF could be executed by adding Application.Volatile to force the update.

    Function My_Func() 
 Application.Volatile 
 ' 
 ' Remainder of the function 
 ' 
End Function

    As per the documentation:

    Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

    In your code:

    Public Function ColorIndex(CellColor As Range)
 Application.Volatile 
 ColorIndex = CellColor.Interior.ColorIndex
End Function