Count if a cell has color shading

[robnsd]

Is there a way to count a cell if it has color shading? I have no
values in my cells, but they are either shaded a color or not. I only
want to count the cells that have color shading in the range I select.
Is this possible? I would rather not enter values or text in the cells
to count them.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jlclyde]

Is there a way to count a cell if it has color shading? I have no
values in my cells, but they are either shaded a color or not. I only
want to count the cells that have color shading in the range I select.
Is this possible? I would rather not enter values or text in the cells
to count them.

Function CountColor(Color As Range, Range As Range) As Long
Dim C As Range
For Each C In Range
If C.Interior.ColorIndex = Color.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function

I do not knwo how much you know about VBA so here goes. I am assumign
that you want to use this function on multiple workbooks and not just
the one right now. You will need to save this function to your
personel macro work book. To do this you need to go to tools, Macro,
record new macro, choose personel macro workbook, Hit the little stop
button or go to tools macro stop. Go to Window, Unhide, personel
macro workbook. As it is unhid click Alt+F11. This will open the VBA
window. Insert Module. Highlight the function from this posting and
copy into your module.
To use the formula type =COUNTCOLOR( Then click on cell color that
you want to know the count of, Click the range that you want to
count.
Jay