VBA If/then on each cell in a range on multiple worksheets

[Lost_in_Excel]

Hi. I download a large dataset each month, do a little processing into multiple sheets (>50). Each line of the various sheets is reviewed for significance and the cell in column A is color coded if a line needs to be investigated further. Once checked the colors are cleared and an archive copy is created.

What I'm trying to do is to loop through all the sheets to remove these colors prior to creating the archive (other than the final 7 control sheets). Ideally I'd like a macro to scan to entire used range on each sheet, as sometimes entries other than in column A are colored, but that's just a little beyond me.

I've written a macro, which doesn't work, but I don't understand why. Can anyone help please?
PS I've tried to exclude black and dark blue from being cleared as these colors are used in the headers.

Sub Reset_Cell_Shading()

Dim I As Integer
Dim cell As Range

Application.ScreenUpdating = False
For I = 1 To ThisWorkbook.Worksheets.Count - 7
    For Each cell In Range("A1:A250")
    If cell.Interior.ColorIndex <> 1 Or cell.Interior.Color <> RGB(0, 0, 128) Then
    cell.Interior.ColorIndex = 0
    End If
    Next cell
Next I
Application.ScreenUpdating = True
End Sub

 

[cmowla]

Why do you have -7 here? That's probably what's wrong:

ThisWorkbook.Worksheets.Count - 7

If you possibly wanted to exclude sheets, you can filter them out by name:

    If Sheets(I).Name <> "Sheet Not to touch" Then
        For Each cell In Sheets(I).Range("A1:A250")
        If cell.Interior.Color <> RGB(0, 0, 0) Or cell.Interior.Color <> RGB(0, 0, 128) Then
            cell.Interior.ColorIndex = 0
        End If
    End If

Or if you wanted to only touch the visible sheets, there's a filter you can set for that:

    If Sheets(I).Visible = -1 Then
        For Each cell In Sheets(I).Range("A1:A250")
        If cell.Interior.Color <> RGB(0, 0, 0) Or cell.Interior.Color <> RGB(0, 0, 128) Then
            cell.Interior.ColorIndex = 0
        End If
    End If

Also, you need to have Sheets(I) in front of range, because it will otherwise just take reset the colors of the active sheet. (I am assuming that Range("A1:A250") is on all sheets . . . that all sheets have the same used range, etc.):

For Each cell In Sheets(I).Range("A1:A250")

---
So the code can read:

Sub Reset_Cell_Shading()

Dim I As Integer
Dim cell As Range

Application.ScreenUpdating = False
For I = 1 To ThisWorkbook.Worksheets.Count
    For Each cell In Sheets(I).Range("A1:A250")
        If cell.Interior.Color <> RGB(0, 0, 0) Or cell.Interior.Color <> RGB(0, 0, 128) Then
            cell.Interior.ColorIndex = 0
        End If
    Next cell
Next I
Application.ScreenUpdating = True

End Sub

 

[Lost_in_Excel]

Why do you have -7 here? That's probably what's wrong:

ThisWorkbook.Worksheets.Count - 7

If you possibly wanted to exclude sheets, you can filter them out by name:

    If Sheets(I).Name <> "Sheet Not to touch" Then
        For Each cell In Sheets(I).Range("A1:A250")
        If cell.Interior.Color <> RGB(0, 0, 0) Or cell.Interior.Color <> RGB(0, 0, 128) Then
            cell.Interior.ColorIndex = 0
        End If
    End If

Or if you wanted to only touch the visible sheets, there's a filter you can set for that:

    If Sheets(I).Visible = -1 Then
        For Each cell In Sheets(I).Range("A1:A250")
        If cell.Interior.Color <> RGB(0, 0, 0) Or cell.Interior.Color <> RGB(0, 0, 128) Then
            cell.Interior.ColorIndex = 0
        End If
    End If

Also, you need to have Sheets(I) in front of range, because it will otherwise just take reset the colors of the active sheet. (I am assuming that Range("A1:A250") is on all sheets . . . that all sheets have the same used range, etc.):

For Each cell In Sheets(I).Range("A1:A250")

---
So the code can read:

Sub Reset_Cell_Shading()

Dim I As Integer
Dim cell As Range

Application.ScreenUpdating = False
For I = 1 To ThisWorkbook.Worksheets.Count
    For Each cell In Sheets(I).Range("A1:A250")
        If cell.Interior.Color <> RGB(0, 0, 0) Or cell.Interior.Color <> RGB(0, 0, 128) Then
            cell.Interior.ColorIndex = 0
        End If
    Next cell
Next I
Application.ScreenUpdating = True

End Sub

Thank you so much, this works perfectly!

That was driving me insane. It was the Sheets(I) bit that was the real sticking point. I thought the For/next loop would make each sheet in turn the active sheet. You live and learn.

PS the -7 after the "ThisWorkbook.Worksheets.Count" was so that the the loop would ignore the last 7 sheets on the workbook. I got that from a lot of googling.

Thanks you!

 

[cmowla]

PS the -7 after the "ThisWorkbook.Worksheets.Count" was so that the the loop would ignore the last 7 sheets on the workbook. I got that from a lot of googling.

I thought so, hence why I mentioned that you can filter out the specific sheets that you don't want to touch by name. It's a better way of handling it, because it's always possible that the order of the sheets will change!