Dynamically Changing Worksheet Tab Color

Andrew knows how to change the color of worksheet tabs manually. However, he would like a way to change the tab color based upon a value in a cell on the worksheet.

To do this, you'll need to use a macro. The key is that you want to change the Color property of the Tab object, in this manner:

ActiveSheet.Tab.Color = vbRed

The logic you use to get to the point of making such a color assignment depends on what you want to do and when you want to do it. Assuming that you want to change the tab color based on what is in cell A1, you could use a macro like the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        MyVal = Range("A1").Text

        With ActiveSheet.Tab
            Select Case MyVal
                Case "0"
                    .Color = vbBlack
                Case "1"
                    .Color = vbRed
                Case "2"
                    .Color = vbGreen
                Case "3"
                    .Color = vbYellow
                Case "4"
                    .Color = vbBlue
                Case "5"
                    .Color = vbMagenta
                Case "6"
                    .Color = vbCyan
                Case "7"
                    .Color = vbWhite
                Case Else
                    .ColorIndex = xlColorIndexNone
            End Select
        End With
    End If
End Sub

You need to add the macro to the code for the worksheet whose tab you want to modify. (Right-click the sheet's tab and choose View Code from the Context menu. Paste the code into that code window.) The macro grabs whatever is in cell A1 and then uses a Select Case structure to change the color of the tab. The logic changes the color if A1 contains 0 through 7. If there is anything else there (or nothing at all), then the ColorIndex property is used to set the tab color back to its default.

The macro could be modified so that it checks for a change in a cell other than A1; just modify the first line that checks the .Address property of the Target object. It could also be changed so that what it tests for is a text string (such as "Black", "Red", etc.) or some keyword (such as "Low" or "High"). You could also use different color designations with the Color property, such as the RGB function:

                .Color = RGB(255, 0, 0)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13138) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.