Counting Shaded Cells in Excel

[AGlazer]

Hey guys.

I got an odd question from someone in my office the other day and I wanted to see if anyone had any suggestions. He wants to be able to count the total number of shaded cells in a row and/or column. I'm comfortable using the count functions, but I'm clueless how to approach this to look at shaded cells. Any and all suggestions would be greatly appreciated!

Thanks,

Aaron

 

[Zathras]

It can be done with a VBA function, but there are a couple of wrinkles.

Paste this macro in a module in the workbook. If you are new to VBA, Press Alt-F11 then from the menu: Insert/Module. You can then close the VBA editor.

Function CountShaded(Optional ByVal X As Double = 0) As Double
Dim c As Range
For Each c In ThisWorkbook.ActiveSheet.UsedRange
  If c.Interior.ColorIndex <> xlNone Then CountShaded = CountShaded + 1
Next c
End Function

Now paste this function into any cell:

  =CountShaded(RAND())

When additional cells are shaded, this does not automatically update. You can press F9 to refresh. On the other hand, anytime the worksheet does recalc, the shaded cells will be re-counted. This could cause a performance problem if the worksheet is very large. (The number of shaded cells is immaterial, the entire worksheet is scanned each time it is recalculated.)

 

[Hasit]

Have a look at this link, it shows you how to count cells based on whatever colour you choose:

http://www.cpearson.com/excel/colors.htm