Written by Allen Wyatt (last updated February 1, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
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)
Note:
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When worksheet names are quite long, it can present problems in displaying those names in the tabs at the bottom of the ...
Discover MoreNeed to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can ...
Discover MoreNeed to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments