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 !
HTH
Chris
Posted by DK on January 11, 2002 3:23 AM
Hello,
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
Next
End Function
Hope this helps,
D.
Posted by Juan Pablo G. on January 11, 2002 5:47 AM
