sum up cells by background colour

Hi all,

I would like to sum up certain cells depending on the background colour they have. On the attachment you will find an example where values need to be summed up according to the background colour. I found the following macro online which enables me to do that. However every time I want to add a values in the range of F9 to F30 and change the background colour the sums do not update automatically.
Attachment 168438
Function SumColor(Color As Range, Range As Range) As Long

Dim Cell As Range
Dim ColorIndexNumber As Integer
Dim ColorSum

ColorIndexNumber = Color.Interior.ColorIndex

For Each Cell In Range
If Cell.Interior.ColorIndex = ColorIndexNumber Then
ColorSum = WorksheetFunction.Sum(Cell.Value) + ColorSum
End If
Next Cell

SumColor = ColorSum

End Function

I would appreciate your help as I am stuck and cant continue.

I am looking forward to hearing from you.

Custom functions don't automatically update when you change the format (color) of a cell. They will automatically update if you change the value of a cell.

An imperfect solution: Add this at the top of the macro, it will recalculate the custom function when any value (not background color) on the sheet is changed. You could also press the F9 key to force the custom functions to update.

Function SumColor(Color As Range, Range As Range) As Long
Application.Volatile
Dim Cell As Range
Dim ColorIndexNumber As Integer
Dim ColorSum

ColorIndexNumber = Color.Interior.ColorIndex

For Each Cell In Range
    If Cell.Interior.ColorIndex = ColorIndexNumber Then
        ColorSum = WorksheetFunction.Sum(Cell.Value) + ColorSum
    End If
Next Cell

    SumColor = ColorSum
    
End Function

Excellent Thank you.