Count colored cell on excel automatically

Question

I'm trying to count the number of cells based on colors, currently using a VBA code that already does it. But it doesnt update automatically and I will have to manually keep refreshing it. Any way to fix this?

Thank you!!

Current VBA code:

Function CountCellColor(CountRange As Range, CountColor As Range)
Dim Color As Integer
Dim Total As Integer
Color = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = Color Then
Total = Total + 1
End If
Next rCell
CountCellColor = Total
End Function

Answer 1

Use Filter and SUBTOTAL to Count Colored Cells
Use the following formula in any cell below the data set: =SUBTOTAL(102,E1:E20)
Choose the headers.
Filter may be found under Data –> Sort and Filter –> Filter. A filter will be applied to all of the headers as a result of this.
Any of the filter drop-downs can be selected by clicking on it.
Select the colour from the 'Filter by Color' menu.