vba code updating with changes to cell color

[xjpx]

Hi all, I have a code below that counts the number of coloured cells of a specific colour. The problem is that it does not recalculate when the cell colour changes. It only updates when I type something in the sheet. From what I understand this is due to

Application.Volatile

This does not trigger recalculation due to cell colour changes. Is there a way to make my code trigger a recalculation when there are cell colour changes?

Function ColorFunction(rColor As Range, rRange As Range)
Dim rCell As Range
Dim lCol As Long
Dim vResult

Application.Volatile

lCol = rColor.Interior.ColorIndex
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell

ColorFunction = vResult
End Function

Thanks!

 

[Fluff]

Is there a way to make my code trigger a recalculation when there are cell colour changes?

No, there is no way to detect that the colour of a cell has been changed. The best you can do is to use a selection change event to recalculate the sheet, but that could cause your workbook to slow down.