Macro Not Looping Through All Worksheets

RmBaugh

New Member
Joined
Feb 1, 2014
Messages
45
Hello everyone,

I try and not ask too many questions here because I can usually find answers specifically for or close enough to modify for my needs.
However, I am stumped on this one.

I have a workbook that I work off of weekly.
I have a Main Page and then tabs for each state.
Around 30 tabs total.

I have worked out the macros to change the row colors based on the date.
But, I cannot get it to loop through all of the worksheets.
I have tried several ways without luck.
I can go to each page and run the macro and it works, but just does not loop.

There are different codes that I run that loops with any trouble.
I have tried to incorporate this particular code into them to try and get it to work.
It will successfully run them and leave this out.

Any help will be greatly appreciated.

HTML:
Sub GetLate2()
Dim Late As Range
For Each ws In WorksheetsIf ws.Name <> "All Open Sales Orders" Then
Set Late = Range("l2:l500")          
For Each Cell In Late      
Select Case Cell.Value          
Case Is = "1"            
Cell.EntireRow.Font.Color = -16776961                      
Case Else            
Cell.EntireRow.Interior.ColorIndex = xlNone                 
 End Select          
Next    
End If    
Next ws    
End Sub

This works if ran individually on each tab.
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
This is what I tried originally.

****Sub GetLate()Dim ws As Worksheet
For Each ws In Worksheets
Range("A2:K1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$C2:$C1000<today()"
Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Next ws
End Sub*****
<today()"
<today()"


<today()" =""="" selection.formatconditions(selection.formatconditions.count).setfirstpriority="" with="" selection.formatconditions(1).font="" .color="-16776961" .tintandshade="0" end="" selection.formatconditions(1).stopiftrue="False" next="" wsend="" sub[="" html]<="" div="">This too, also works if ran on each tab individually.
Sorry for the asterisks.
Cannot get the code to wrap.?</today()"></today()"
</today()"
</today()"
 
Last edited:
Upvote 0
You need to change everywhere it says Cells in the loop to ws.Cells in the first code.
 
Last edited:
Upvote 0
It throws
"Compile error:
Variable required - can't assign to this expression"

When I made the changes.
 
Upvote 0
Try..

Code:
Sub GetLate2()
Dim Late As Range
For Each ws In WorksheetsIf ws.Name <> "All Open Sales Orders" Then
Set Late = ws.Range("l2:l500")          
For Each Cell In Late      
Select Case Cell.Value          
Case Is = "1"            
Cell.EntireRow.Font.Color = -16776961                      
Case Else            
Cell.EntireRow.Interior.ColorIndex = xlNone                 
 End Select          
Next    
End If    
Next ws    
End Sub
 
Upvote 0
You should also declare ws....

Code:
Sub GetLate2()
    Dim Late As Range, ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "All Open Sales Orders" Then
            Set Late = ws.Range("l2:l500")
            For Each Cell In Late
                Select Case Cell.Value
                    Case Is = "1"
                        Cell.EntireRow.Font.Color = -16776961
                    Case Else
                        Cell.EntireRow.Interior.ColorIndex = xlNone
                End Select
            Next
        End If
    Next ws
End Sub
 
Upvote 0
You're welcome and apologies for miss-reading on my phone Cell for Cells :(
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top