PDA

View Full Version : [SOLVED] Changing tab colors based on a list of tabs and cell value



oam
08-11-2015, 08:09 PM
I need to change the tab color to yellow of each worksheet (Detroit, Michigan) I have in a list if the corresponding shipper is DHL and DHL is listed in corresponding row. Also, I need to change the tab color to blue of each worksheet (Seattle, Washington) I have in a list if the corresponding shipper is FedEx and FedEx is listed in corresponding row.

Is this do able?



Row

A

B



1

Detroit, Michigan

DHL



2

Seattle, Washington

FedEx

oam
08-13-2015, 03:59 PM
Anyone have an idea on how to make this work or can I provide additional information that would help?

Paul_Hossler
08-13-2015, 07:21 PM
Requirements are fuzzy, but maybe something like this?



Option Explicit
Sub ColorTabs()
Dim ws As Worksheet
Dim sShipper As String


For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Map" Then

On Error GoTo NotDefined
sShipper = Application.WorksheetFunction.VLookup(ws.Name, Worksheets("Map").Range("A:B"), 2, False)
On Error GoTo 0

If sShipper = "DHL" Then
ws.Tab.Color = vbBlue
ElseIf sShipper = "FedEx" Then
ws.Tab.Color = vbYellow
End If
End If
NotDefined:
Next

End Sub

oam
08-17-2015, 03:34 PM
This work for changing tab colors but I am still looking for the code to change the tab back to orginal configuration.