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:

RmBaugh

New Member
Joined
Feb 1, 2014
Messages
45
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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,490
Office Version
365, 2010
Platform
Windows, Mobile
You need to change everywhere it says Cells in the loop to ws.Cells in the first code.
 
Last edited:

RmBaugh

New Member
Joined
Feb 1, 2014
Messages
45
It throws
"Compile error:
Variable required - can't assign to this expression"

When I made the changes.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,490
Office Version
365, 2010
Platform
Windows, Mobile
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,490
Office Version
365, 2010
Platform
Windows, Mobile
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,490
Office Version
365, 2010
Platform
Windows, Mobile
You're welcome and apologies for miss-reading on my phone Cell for Cells :(
 

Forum statistics

Threads
1,082,253
Messages
5,364,045
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top