Count conditional formatting

[Bradzo]

Hi Everyone,

I sometime ago made a spreadsheet with the below Marco to count the conditional formatted cells in a set row. I now I need to amend the sheet to have it run in all the rows below and I can't work out hoe to adapt the code. Each attempt has resulted in failure and I'm reaching out for assistance.

I would like to continue down the the original range of P9:AK9 to P10:AK10 and so on until P45:AK45 and return each row result into AL9: to AL45.

Thanks, Brad


CountColorCells()
Dim rng As Range
Dim lColorCounter As Long
Dim rngCell As Range
'Set the range
Set rng = ("Site SOP Sign-off").Range("P9:AK9")
For Each rngCell In rng
If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(0, 176, 80) Then
lColorCounter = lColorCounter + 1
End If
Next
Sheets.("Site SOP Sign-off").Range("AL9") = lColorCounter
End Sub

 

[jmacleary]

Hello there. I have corrected a couple of typos, and added the loop you want whilst keeping as much of your code unchanged as possible.

Sub CountColorCells()
    Dim rng As Range
    Dim lColorCounter As Long
    Dim rngCell As Range
    Dim NextLine As Long
'Set the range
    For NextLine = 9 To 45
        lColorCounter = 0
        Set rng = Sheets("Site SOP Sign-off").Range("P" & NextLine & ":AK" & NextLine)
        For Each rngCell In rng
            If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(0, 176, 80) Then
                lColorCounter = lColorCounter + 1
            End If
        Next rngCell
        Sheets("Site SOP Sign-off").Range("AL" & NextLine) = lColorCounter
    Next NextLine
End Sub

 

[Bradzo]

Works perfectly, thank-you