help

[cmccabe]

Hello,

When I use the code below I get there are 1 cells with the red background color. The original cell range has A1:A4 red so why is the code not returning 4? Thanks.

Sub CountCells()
    ' Count Cells with content or with background coloring
    Dim c As Range, Cnt As Integer
    Cnt = 0
        For Each c In Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 256))
        If c.Interior.ColorIndex = 3 Then Cnt = Cnt + 1
    Next c
         MsgBox "There are " & Cnt & " cells that have a red background color"
End Sub

 

[AlphaFrog]

The macro loops through all the cells in one row. A1:A4 are in a column.

This loops through all cells in the activecell row.

For Each c In Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 256))

 

[cmccabe]

Can the code be modified for columns? Thanks.

 

[AlphaFrog]

For Each c In Range(Cells(1, ActiveCell.Column), Cells(100, ActiveCell.Column))

 

[ScottR]

For Each c In Range(Cells(firstrow,ActiveCell.Column), Cells(lastrow, ActiveCell.Column))

where firstrow and lastrow correspond to the first and last rows you wish to loop through (ie substitute numbers or your numeric variables)

 

[cmccabe]

That works great, thank you.

If I also wanted to count the green cells and yellow cells in addition to the red is that possible? Thanks.