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)