MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting Shades

Posted by Ampleford on January 11, 2002 2:29 AM

I'm trying to create a holiday planner (of sorts) and want to know if I can get Excel to count cells that are shaded........

Posted by Chris D on January 11, 2002 3:15 AM

There's much better VBA code for this, I'm sure, but as a non-VBA person, I'd currently use conditional formatting and COUNTA :

rather than shade your vacations, put a "1" in the relevant days

then conditional format the range so that if the cell value is not equal to 0, the font is pink and the background pattern is pink (effectively shading the whole cell) - this will give you pink ranges of vacation days

then use =COUNTA to count non-blank cells on each person's vacation range

Pretty ameuter, I know !

Posted by DK on January 11, 2002 3:23 AM


There isn't a built in function to count colours in Excel. However, it's easy to implement using a bit of VBA to create a UDF.

Function CountShadedCells(CountRange As Range) As Long
'Function to count the number of shaded cells in the
'selected range.

Dim rngeCell As Range

For Each rngeCell In CountRange.Cells
If rngeCell.Interior.ColorIndex <> xlColorIndexNone Then
CountShadedCells = CountShadedCells + 1
End If

End Function

Hope this helps,

Posted by Juan Pablo G. on January 11, 2002 5:47 AM

I posted this UDF on Wednesday


Juan Pablo G.