Conditional Colouring of Excel Sheet Tabs with VBA

Gašper Kamenšek

February 20, 2018
0 Comments

Ever wondered if your could use conditional colouring on Excel sheet tabs? Of course! This post will feature VBA code that will recolour the Worksheet tab based on a set of predefined conditions. Let’s imagine we have a Workbook where each Sheet represents one active project in a company. Cell A1 gives a short status of the project from the task scheduling aspect. The three statuses are

On Time
Slight Delay
Delayed

The VBA code will go through all the (Projects) Sheets in the Workbook, check the value (status) of cell A1 and if the status of the Project (Value of cell A1) is “On Time”, the Sheet tab colour will be set to Green, if it’s “Slight Delay”, the colour will be Yellow and if it’s “Delayed”, Red colour will be applied. Conditional Colouring of Excel Sheet Tabs with VBA

Conditional Colouring of Excel Sheet Tabs with VBA

So here is the VBA code.

Sub ConditionalFormattingSheetTabs()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
ProjectStatus = sh.Cells(1, 1).Value
Select Case ProjectStatus

Case "On Time"
sh.Tab.Color = 5287936

Case "Slight Delay"
sh.Tab.Color = 65535

Case "Delayed"
sh.Tab.Color = 192

End Select

Next sh

End Sub

This is more or less just a proof of concept code. Although, in project management this would be nice to see. I could also easily see this applied in a Workbook that has Sheets with imported data, intermediate calculation Sheets and final report Sheets which are usually (for organisational purposes) colour coded. And if someone really wants to spent a Weekend on it you could have Conditional Formatting applied to cells and then have VBA code run through those cells and figure out which is the dominant formatting (in sense of colour) and apply that to the Sheet tab. So lots of applications for this… Here is the original Workbook so you can play with the code and see it in action. (Keep in mind the code was written in Excel 2016). To sum up, this is how you use conditional colouring on Excel sheet tabs. Stay tuned for more great tricks!