Macro Not Looping Through All Worksheets

[RmBaugh]

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.

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.

 

[RmBaugh]

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()"

 

[MARK858]

You need to change everywhere it says Cells in the loop to ws.Cells in the first code.

 

[RmBaugh]

It throws
"Compile error:
Variable required - can't assign to this expression"

When I made the changes.

 

[MARK858]

Try..

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

 

[MARK858]

You should also declare ws....

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

 

[RmBaugh]

MARK858,
Thank you so much!!!
It works perfectly.

 

[MARK858]

You're welcome and apologies for miss-reading on my phone Cell for Cells