oam
09-03-2015, 06:07 PM
I am using the code shown below to change the tab colors if the shipper is DHL and the location is on the list. What I would like to know is how to tell the code to stop trying to change tab colors when it gets to the end of the list. When I run the code I get an error when it gets to the end of the list and all the tab colors are changes instead of stopping.
Thanks for any and all help.
Sub ColorTab()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim MyCell As Range, myRange As Range
'ActiveWorkbook.Sheets("Locations").Range ("F2")
Set myRange = Sheets("Locations").Range("D2")
Set myRange = Range(myRange, myRange.End(xlDown))
For Each MyCell In myRange
If Sheets(MyCell.Text).Range("AB2") = "DHL" Then
Sheets(MyCell.Value).Tab.ColorIndex = 33
End If
Next MyCell
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thanks for any and all help.
Sub ColorTab()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim MyCell As Range, myRange As Range
'ActiveWorkbook.Sheets("Locations").Range ("F2")
Set myRange = Sheets("Locations").Range("D2")
Set myRange = Range(myRange, myRange.End(xlDown))
For Each MyCell In myRange
If Sheets(MyCell.Text).Range("AB2") = "DHL" Then
Sheets(MyCell.Value).Tab.ColorIndex = 33
End If
Next MyCell
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub