PDA

View Full Version : [SOLVED] For Loop goes thru all sheets but doesn't execute code?



ajjava
06-04-2019, 01:37 PM
I'm sure I'm just not seeing the forest for the trees - this should be so simple....

I just want to change the color of the blue cells to white. I need to loop through all used ranges of all worksheets. The code I have is going through all the sheets, but it only executes the code on the specific worksheet that's active when I run the macro. What am I missing?


Sub ChangeColor()Dim ws As Worksheet
Dim cell As Range
Dim rng As Range


Set rng = Range("A1:aj200")
Set ws = Application.ActiveSheet


For Each ws In Worksheets
ws.Activate

For Each cell In rng

If cell.Interior.ColorIndex = 23 Then
cell.Interior.ColorIndex = 2
cell.Font.ColorIndex = 56
End If
Next cell
Next ws


End Sub

austenr
06-04-2019, 01:46 PM
Try using a loop like this:

Your code was only looking at the existing sheet not all.


For each sh in ActiveWorkbook.Sheets

If sh.name <> "IgnoredSheet" Then

' Run Code

End If

Next sh

p45cal
06-04-2019, 02:53 PM
That is because you set rng only once outside the loop. It doesn't change after that at all, it's always the same range on the same sheet.
Move the line:
Set rng = Range("A1:aj200")
to directly beneath the ws.Activate line.
The following line is redundant:
Set ws = Application.ActiveSheet

But you don't need to activate anything, you could:
Sub ChangeColor()
Dim ws As Worksheet, cell As Range

For Each ws In Worksheets
For Each cell In ws.Range("A1:aj200")
If cell.Interior.ColorIndex = 23 Then
cell.Interior.ColorIndex = 2
cell.Font.ColorIndex = 56
End If
Next cell
Next ws
End Sub

ajjava
06-05-2019, 05:42 AM
Ok, I see. At first, the procedure wasn't looping through all of the sheets, either, and I found advice that suggested to add the Activate line.

I'm really struggling with the correct syntax and logic in VBA, in general. Just when I think I've got it, I screw up a silly, small thing. Never-ending learning process, I guess. Thank you so much!!!