Counting shaded boxes

Originally Posted by cat85

Hi,

I was wondering if there was a quick way to count the number of shaded boxes in a column? There are currently no numbers in these boxes and I have about 5-10 columns per worksheet.

I thought I could make a tally of shaded boxes at the bottom of each column or use a formula to put a number in each shaded box and take a count of those? Alternatively sort the data so the shaded boxes are clumped (currently they are spread out).

Unfortunately I am not sure if any of these are possible?

Thanks!

Hi,

a macro something like

Sub CountInterior()
Dim c As Range
Dim iRow As Long, iSubRow As Long
Dim iTotA As Long, iTotB As Long, iTotC As Long, iTotD As Long, iTotE As Long
iSubRow = 100
iTotA = 0: iTotB = 0: iTotC = 0: iTotD = 0: iTotE = 0
For iRow = 1 To iSubRow
    If Cells(iRow, 1).Interior.ColorIndex > 0 Then iTotA = iTotA + 1
    If Cells(iRow, 2).Interior.ColorIndex > 0 Then iTotB = iTotB + 1
    If Cells(iRow, 3).Interior.ColorIndex > 0 Then iTotC = iTotC + 1
    If Cells(iRow, 4).Interior.ColorIndex > 0 Then iTotD = iTotD + 1
    If Cells(iRow, 5).Interior.ColorIndex > 0 Then iTotE = iTotE + 1
Next
Cells(iSubRow, 1).Value = iTotA
Cells(iSubRow, 2).Value = iTotB
Cells(iSubRow, 3).Value = iTotC
Cells(iSubRow, 4).Value = iTotD
Cells(iSubRow, 5).Value = iTotE
End Sub

should do the trick.

Tools, Macro, Macros, enter the name of the macro, Create and copy the code to the window shown.

hth
---
added, aaha, beaten to the punch.
---