Count Cells Based On A Background Color

**NSTurk725** · 04-27-2010, 10:51 AM

Greetings,

I am having trouble assembling some code to count the cells that have a background (interior index) of red in the worksheet range("J:AE")

If anyone could lend me some assistance, I would be very grateful. Thank you in advance.

Here is what I have developed so far, but it's not working as planned.

Sub Count_Changes()
Dim Changer As Integer
Changer = 1
Sheets("Price+Qty_Changes").Select
For Each X In Range("J:AE")
    If X.Interior.ColorIndex = 3 Then  'just want to count cells with background of red
    Changer = Changer + 1
    End If
Next X
MsgBox(Changer)
End Sub

**contaminated** · 04-27-2010, 11:05 AM

Ok try this one

Sub Test()
    Dim Counter As Long
    Dim c, r As Long
    Dim RowsCount, ColumnsCount As Long
    
    RowsCount = Sheets("Price+Qty_Changes").Range("J:AE").Rows.Count
    ColumnsCount = Sheets("Price+Qty_Changes").Range("J:AE").Columns.Count
    
    For c = 1 To ColumnsCount
        For r = 1 To RowsCount
            If  Sheets("Price+Qty_Changes").Cells(r, c).Interior.ColorIndex = 3 Then
                Counter = Counter + 1
            End If
        Next r
    Next c
    MsgBox Counter
End Sub

But it takes too much time to loop...

**NSTurk725** · 04-27-2010, 11:16 AM

I ran the code with some sample data. However, it came back with a total count of 0, when it should have come back with 4. Any thoughts as to why it might not be detecting or counting the cells with a red background? Thank you your help. I appreciate it.

**contaminated** · 04-27-2010, 11:29 AM

I ran the code with some sample data. However, it came back with a total count of 0, when it should have come back with 4. Any thoughts as to why it might not be detecting or counting the cells with a red background? Thank you your help. I appreciate it.

It works for me.

See file

HTH

**NSTurk725** · 04-27-2010, 11:26 AM

Hey Contaminated,

Never mind my previous post. Found on a mistake on my end. Your code works perfectly. Thank you for your help. I appreciate it greatly.

Respectfully,

NSTURK725