How to create a one-click macro to color-code tabs based on the contents of the sheet name?

Question

I have a macro to import several files into one sheet and give them names with legal characters. These files and filenames will vary from time to time depending on the needs of the project. I would like to color code like sheets by sheet name.

So I need to loop through all sheets looking for particular phrases. Like all sheets containing the word "page" would be colored blue. So Page Title Duplicates and Page Titles Too Long would both be blue tabs. While pages containing the phrase H1 would all be red. So H1 Duplicates and H1 Too Long would both be red tabs.

There are times a title would include both page and H1, so I need to make an exception for that and chose a unique color.

I realize I need to loop through all sheets checking for their existence and giving a case as to what the name contains and what color I want to use, but I can't seem to find a one-click macro that isn't a private sub to accomplish this.

Sub TabColor()

Sheets("canonicals missing").Tab.Color = vbBlack
Sheets("canonicals nonindexable canonic").Tab.Color = vbBlack
Sheets("h1 duplicate").Tab.Color = vbRed
Sheets("h1 missing").Tab.Color = vbRed
Sheets("h1 multiple").Tab.Color = vbRed
Sheets("h1 over 70 characters").Tab.Color = vbRed
Sheets("page titles duplicate").Tab.Color = vbBlue
Sheets("page titles missing").Tab.Color = vbBlue
Sheets("page titles over 65 characters").Tab.Color = vbBlue
Sheets("page titles same as h1").Tab.Color = vbBlue

End Sub

I chose the exact title route because there are times they contain two keywords like "page" and "h1", so I figured it's better to be explicit. However, if those titles don't exist then it stops. The titles will always vary so there's no way to ensure exactly what the titles will be in any given batch of .xls files. I have no clue how to loop through each sheet and check for its existence. I'm very new to VBA and I'm stumbling.

Answer 1

Something like this maybe?

Using a for each loop on the worksheets collection and instr to search a string in a string.

Sub test_tabcol()

Dim w As Excel.Worksheet

For Each w In ThisWorkbook.Worksheets

    If InStr(1, w.Name, "search for blue", vbTextCompare) <> 0 Then
        w.Tab.Color = vbBlue
    ElseIf InStr(1, w.Name, "search for red", vbTextCompare) <> 0 Then
        w.Tab.Color = vbRed
    Else
        w.Tab.Color = xlNone
    End If

Next w

End Sub

Answer 2

Here's a method that colors tabs based upon the background color of cells in an index sheet. The advantage of this approach is that you can modify search text and the colors easily. The animated gif (click for detailed view) shows me stepping through the code and the sheet tabs being colored based on the text in the colored cells. Of course, you would modify the code to suit your needs.

Sub colorTabs()
Dim sh As Worksheet, r As Range
Set sh = ThisWorkbook.Worksheets("index")
Set r = sh.Range("A1")
While r <> ""
    For Each sh In ThisWorkbook.Worksheets
        If InStr(sh.Name, r) Then sh.Tab.ColorIndex = r.Interior.ColorIndex
    Next sh
    Set r = r.Offset(1, 0)
Wend
End Sub

Answer 3

As you colored the tabs which do not contain page or H1 in vbBlack in your example I assume you are after something like that

Sub ColorIt()

    Dim wks As Worksheet

    For Each wks In ActiveWorkbook.Worksheets

        ' Black for all sheets
        wks.Tab.Color = vbBlack

        ' Blue in case it contains 'page'
        If InStr(1, wks.Name, "page") Then
            wks.Tab.Color = vbBlue
        End If

        ' Red in case it contains 'H1'
        If InStr(1, wks.Name, "H1") Then
            wks.Tab.Color = vbRed
        End If

        ' Cyan (or another color) in case it contains 'page' and 'H1'
        If InStr(1, wks.Name, "page") > 0 And InStr(1, wks.Name, "H1") > 0 Then
            wks.Tab.Color = vbCyan
        End If

    Next wks

End Sub