by Allen Wyatt
(last updated July 13, 2019)
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) 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 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 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, and Excel in Office 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information ...Discover More
Got a workbook with a lot of worksheets in it? Here are some handy ways to jump to the worksheet you want, alphabetically.Discover More
As you get more and more worksheets into a workbook, you'll find yourself moving them around into different sequences. ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.