SumbyColor not automatically updating

[MattB]

Hi,

I've been using the following.... however it soes not auto update if I change a cell colour. Any advice would be most appreciated please! Many thanks.

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

 

[Yongle]

Changing a colour-fill manually does not trigger any event in the worksheet
{F9} forces sheet recalculation and will update the formula

 

[Cocoba]

Hi - I'm having the same issue with my Sumbycolor function below, when I change cell color, it does not update, any help with this would be much appreciated

Function sumbycolor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer

Application.Volatile
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
sumbycolor = cSum

End Function