Lost_in_Excel
New Member
- Joined
- Oct 22, 2010
- Messages
- 20
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.
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.
VBA Code:
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