How to change the colour of an Excel Worksheet tab using a cell value with VBA

Changing the colour of a worksheet tab based on a cell value is a useful technique to have up your sleeve.  There are many practical applications for this technique and it is particularly useful for those times when automatically sending a user to another worksheet is considered too intrusive and you would prefer a more subtle approach…

Example Scenario

One implementation of this could be in a questionnaire workbook where an employee answers some questions and depending on how they answer them you want to suggest some additional reading by highlighting the relevant worksheet.

You can see below we have a sample worksheet which contains 3 tabs

  • Questionnaire
  • Happy
  • Sad

Excel How to Change Tab Colour VBA

The Questionnaire worksheet contains 3 questions for an employee to answer, depending on how those are answered we will get a “Happy” or “Sad” result in cell C11 (we use a simple IF statement to achieve that).

If the result is “Happy” then we want to highlight the “Happy” worksheet by changing the tab colour to Green, and if the result is “Sad” we want to change the “Sad” worksheet tab colour to Red.

VBA Code

Our first step is to open up our Visual Basic window (ALT + F11) and in the project window make sure we are writing the script in the “ThisWorkbook” area:

Excel How to Change Tab Colour VBA 2

We need to put the script into that area because it has an impact on the entire workbook; in this case we are changing tab colours.

In the code window we then write the following VBA Script:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Select Case Sheets(“Questionnaire”).Range(“C11”).Value

    Case “Happy”

        Sheets(“Happy”).Tab.ColorIndex = 4

    Case “Sad”

        Sheets(“Sad”).Tab.ColorIndex = 3

    End Select

End Sub

We use the “Select Case” statement to say if the result, which is found on the “Questionnaire” worksheet in Cell “C11”, is “Happy” then on the “Happy” worksheet change the tab colour to Green (Colour index 4).  If the result is “Sad” change the “Sad” worksheet tab colour to Red (Colour index 3).  If you needed to add more logic because you have more sheets and choices you can just continue with another Case statement and so on, and for a full list of colours and the corresponding Excel colour index take a look on the Microsoft Website here.

The Result

Our end result can be seen below.  We’ve found a nice positive employee who has answered “Yes” to all the questions yielding a “Happy” result in cell “C11” and that has changed our tab colour on the “Happy” worksheet to Green:

Excel How to Change Tab Colour VBA 3

Hopefully you’ll find some great uses for this technique in some of your
Microsoft Excel files, why not leave a comment to share with us how you have implemented this script into one of your projects?

Keep Excelling,

Leave a Comment