Hi,

Using Conditional Formatting, I color the cells in a column based on its
value. For Example : If the value is between 0 and 3 then I color the cell as
Green. If the value is between 3 and 4.5 then I color the cell as Yellow. If
the value is greater than 4.5 then I color the cell as Red. This was achieved
using Conditional Formatting. But now I want to count the number of Green,
Red and Yellow cells. For this I created a function to count the cells.

Function ColorFunction(rColor As Range, rRange As Range) As Long
Dim lCol As Long
Dim lActCol As Long
Dim lCount As Long
Dim rCell As Range
Application.Volatile True

lCol = rColor.Interior.ColorIndex

For Each rCell In rRange
lActCol = rCell.Interior.ColorIndex

If lCol = lActCol And rCell.Text < "" Then
lCount = lCount + 1
End If
Next
ColorFunction = lCount
End Function

But some how with conditional formatting the function is not working as the
Interior.ColorIndex is not matching with the rColor cell's ColorIndex.

Is there anyway that I can do to get the count based on the color rather
than doing it manually? If I color the cells manually without conditional
formatting then the function works fine without any problem, but when I
format the cells using conditional formatting it doesn't work.

Thanks,
Gan