Consulting

Results 1 to 4 of 4

Thread: For Loop goes thru all sheets but doesn't execute code?

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    68
    Location

    For Loop goes thru all sheets but doesn't execute code?

    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

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    1,983
    Location
    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
    Peace of mind is found in some of the strangest places.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,669
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Mar 2019
    Posts
    68
    Location
    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!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •