Counting shaded cells
 

I have a very large excel table that my lab uses for test results. A
good portion of the table has been grayed out for various hardware
reasons. I would like to be able to count the number of shaded cells in
the table to get a completion percentage, and im not sure how (is there
a way? :confused: ) withouth manually counting them myself which would
be tremendously tedious.

I am running Excel 2000, but if it made a difference I could upgrade
without a problem if needed.

I have a feeling this might have to be a VB script...

Thank you in advance!


--
demon42
------------------------------------------------------------------------
demon42's Profile: http://www.excelforum.com/member.php...o&userid=27448
View this thread: http://www.excelforum.com/showthread...hreadid=469554

Let's assume that shaded means that the background color index = 48. First
select a pile of cells and then run:

Sub shady()
Dim r As Range
Dim IAmTheCount As Long
Dim j As Integer
IAmTheCount = 0
For Each r In Selection
j = r.Interior.ColorIndex
If j = 48 Then
IAmTheCount = IAmTheCount + 1
End If
Next
MsgBox IAmTheCount
End Sub
--
Gary''s Student


"demon42" wrote:


I have a very large excel table that my lab uses for test results. A
good portion of the table has been grayed out for various hardware
reasons. I would like to be able to count the number of shaded cells in
the table to get a completion percentage, and im not sure how (is there
a way? :confused: ) withouth manually counting them myself which would
be tremendously tedious.

I am running Excel 2000, but if it made a difference I could upgrade
without a problem if needed.

I have a feeling this might have to be a VB script...

Thank you in advance!


--
demon42
------------------------------------------------------------------------
demon42's Profile: http://www.excelforum.com/member.php...o&userid=27448
View this thread: http://www.excelforum.com/showthread...hreadid=469554

Thank you for your help!
Through another forum i frequent, someone found 'this article'
(http://www.cpearson.com/excel/colors.htm) for me and it works great.
Thank you very much anyway, I greatly appreciate it!


--
demon42
------------------------------------------------------------------------
demon42's Profile: http://www.excelforum.com/member.php...o&userid=27448
View this thread: http://www.excelforum.com/showthread...hreadid=469554