VBA 2007 Color Count

[Ritt]

How can I count the cells that have the same color in them using VBA? The cell do not have any other info. Thanks for your help with this.

 

[Ritt]

Ok so now I'm using a UDF to do this but when the colors change in the cells the functions do not update the count. The only way I can get the cell function to update is do it again. This does not work for the end-user.

Function I am using:

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempCount = TempCount + 1
End If
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function