by Allen Wyatt
(last updated May 10, 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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll ...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
If you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...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.