Copy and paste the following code into you VBA project (alt+f10 in excel)

Public Function cell_colour(Cell_Check As Range) As Long
cell_colour = Cell_Check.Interior.Color
End Function

Public Function count_colour(Count_Range As Range, Colour As Long) As Long
Dim x As Range
count_colour = 0
For Each x In Count_Range
If x.Interior.Color = Colour Then
count_colour = count_colour + 1
End If
Next x
End Function

With the formula “count_colour(B1:B10;cell_colour($A$1))” you can count the colorourd cells in B1 to B10 with the same colour as the cell in A1