Counting Shaded Cells
Posted by Jo on January 09, 2002 6:55 AM
My boss has created a completed form. Instead of using #'s, he's shading in the cell next to the part name to show that it's completed. At the bottom he has a total. Is there a formula that will count how many shaded cells are in a column?
Posted by Juan Pablo G. on January 09, 2002 7:05 AM
Jo, try this UDF. It has two arguments, the first one, is the range you want to count (Similar to the range in COUNT or in SUM), and the second one, optional, is to count ONLY those cells that match the color of that selected cell.
Please not that if you change the shading of a cell the function WILL NOT recalculate, because changing formats doesn't generate any events nor will force a recalculation, so you'll have to edit the formula and press enter to get the result.
Function CountShades(Rng As Range, Optional RngColor As Range) As Double
Dim Color As Long
Dim Cll As Range
CountShades = 0
For Each Cll In Rng
If RngColor Is Nothing Then
If Cll.Interior.ColorIndex <> -4142 Then CountShades = CountShades + 1
Else
If Cll.Interior.Color = RngColor.Range("A1").Interior.Color Then CountShades = CountShades + 1
End If
Next Cll
End Function
Juan Pablo G.
