Problems Using For Each Loop with Worksheet Array Macro

[Guest]

I am a novice to Excel VBA and would like to be pointed in the right
direction to solving the problem I am having with the code below. I want to
use the For...Next i Loop for each Worksheet in my Workbook. For some reason
the For Each...Next sh Loop is not working for the worksheets I specified.
The code only works if I physically select each individual worksheet and run
it once per worksheet. PLEASE HELP!! HUGE THANKS IN ADVANCE!!

Sub NEW_COMPILE_SCHEDULES()

Dim sh As Worksheet
LastRow = Cells(65536, 1).End(xlUp).Row

For Each sh In Worksheets(Array("Engineering", "Graph Pro", "Metal Fab",
"Alum Ext"))
For i = LastRow To 5 Step -1
If Cells(i, 10).Font.ColorIndex = 15 Then
Cells(i, 1).EntireRow.Delete
ElseIf Cells(i, 10) = "" Then
Cells(i, 1).EntireRow.Delete
End If
Next i
Next sh

End Sub

 

[Earl Kiosterud]

Ryan

This statement
If Cells(i, 10).Font.ColorIndex = 15 Then
doesn't care which sh the for-each loop is currently working with. It's unqualified, so it
uses the currently active sheet, not sh. It needs to be
If sh.Cells(i, 10).Font.ColorIndex = 15 Then

Same for the other ones.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Roger Govier]

Ryan

In addition to Earl's advice, lastrow is being set outside of the For loop.

Lastrow will be set for whatever sheet is active at the time the sub is
invoked
Is this what you want?
or do you want

Dim Sh as worksheet, lastrow as long

For Each sh In Worksheets(Array("Engineering", "Graph Pro", "Metal Fab",
"Alum Ext"))
LastRow = sh.Cells(65536, 1).End(xlUp).Row

 

[Guest]

I used Earl's advice and your advice as well, but for some reason when I run
the macro it is deleting rows that have black text. It should only delete
entire rows if the cell is blank or grey text in colomn "J". I assigned the
macro to a command button on Sheet8. If I run the macro while I am looking
at "Engineering" it works great for "Engineering" but screws the rest of the
worksheets up. I have looked at this for hours and can't figure it out, any
idea what could be happening? I listed the present code below:

Sub NEW_COMPILE_SCHEDULES()

Dim sh As Worksheet
Dim LastRow As Long

For Each sh In Worksheets(Array("Engineering", "Graph Pro", "Metal Fab",
"Alum Ext", _
"Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab", "Metal
Paint", _
"Thermo", "Tri Graphics", "Deco Faces", "Tri-Face", "LED",
"Crating", _
"Service", "Delivery"))
LastRow = sh.Cells(65536, 1).End(xlUp).Row
For i = LastRow To 5 Step -1
If sh.Cells(i, 10).Font.ColorIndex = 15 Then
sh.Cells(i, 1).ENTIREROW.Delete
ElseIf Cells(i, 10) = "" Then
sh.Cells(i, 1).ENTIREROW.Delete
End If
Next i
Next sh

End Sub

 

[Earl Kiosterud]

Ryan,

You left off one qualifier (sh.) in the ElseIf line.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[Guest]

BAM! PERFECT! This is my first attempt at writing a macro from scratch. I
appreciate your help.