Count Interior Color

[plwandat]

I would like to count all cells in a range that contain an interior color regardless of color.

Thanks
Paul Walton
USAF

[NateO]

The solution to this is different based on conditional formatting or straight-up formatting.

This link is for straight-up formatting:

http://www.mrexcel.com/board/viewtop...c=1585&forum=2

(Bottom post)

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-01 09:50 ]

[plwandat]

Straight-Up Formatting

[NateO]

The link above should be of serious use then. Post back w/ additional questions or concerns.

[plwandat]

Works good for only 1 color. I need it to work for a cell that contains a any color in a range.

[NateO]

Try this:

Function CountByColor(InputRange As Range)
Dim cl As Range, TempCount As Double
Application.Volatile
TempCount = 0
On Error Resume Next
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex <> xlNone Then TempCount = TempCount + 1
Next cl
On Error GoTo 0
Set cl = Nothing
CountByColor = TempCount
End Function

Now just use =countbycolor(a1:z25), and redo the range to your liking.

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-01 10:11 ]

[plwandat]

Thanks... Works as advertised

Paul Walton
USAF