Counting cells by color

[wwgmr]

Hi all question

I have formula that counts cells by their color, my problem is it doesn't count the cell if you enter any numbers into the cell first and then fill the cells color. I can't understand why?

Here is the code.

Function CountColor(rColor As Range, rSumRange As Range)

''''''''''''''''''''''''''''''''''''''
'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

CountColor = vResult
End Function


Is there a better way to write it so that it doesn't matter if you put in number first then fill in cell color? I am using a conditional format for the cells if they are blank and so I cannot fill a color till I have data in the cell.

Any help would be great, I am confused as to why having this problem.

Eric

 

[SkipVought]

Hi,

If you are using Conditional Formatting to color the cells, then use the SAME criteria in your CountColor.

In fact...

you could probably do it with a spreadsheet countif formula.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884