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…
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
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.
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:
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:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sheets(“Questionnaire”).Range(“C11”).Value
Sheets(“Happy”).Tab.ColorIndex = 4
Sheets(“Sad”).Tab.ColorIndex = 3
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.
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:
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?