PDA

View Full Version : [SOLVED] Stopping a macro that uses a list



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

SamT
09-04-2015, 08:03 AM
I don't see anything really wrong with that code, other than the unconventional use of .Text and .Value. If .Value doesn't work in both cases, then use .Text in both cases.

Here is a different version of the same process.

For Each MyCell In myRange
With Sheets(UCase(Trim(MyCell.Value))).Range("AB2")
If UCase(Trim(.Value)) = "DHL" Then .Tab.ColorIndex = 33
End With
Next MyCell

Paul_Hossler
09-04-2015, 08:37 AM
Also try this





For Each MyCell In myRange
If Sheets(MyCell.Text).Range("AB2") = "DHL" Then
Sheets(MyCell.Value).Tab.ColorIndex = 33
Exit For '--------------------------------------------------------------------------------------<<<<<
End If

Next MyCell