Changing tab color of Excel is somehow useful. With VBA, it is easily programmed, as far as one can use the macro recorder to see the correct way to do it. However, if you need to change the color of multiple tabs, then you should simply loop over the tabs.
The basic available theme colors for the tab are the following 12:
However, with the .Color property, the tab colors can be increased to 256^3, which is quite above the level that an average user would ever need.
This is a quick way to color the first 5 tabs on a different color:
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Sub ColorTabs() Dim colors As Variant colors = Array(1, 2, 3, 4, 5) Dim i As Long For i = LBound(colors) To UBound(colors) Worksheets(i + 1).Tab.ThemeColor = colors(i) Next i End Sub |
And if you want to put make all the worksheets in the same color, then simply, change the code to the line below. It would be navy blue:
|
1 |
Worksheets(i + 1).Tab.ThemeColor = 4 |
Pretty much this is all. If you are wondering how to create 100 tabs with 100 different colors, this is a possibility:
|
1 2 3 4 5 6 7 8 9 |
Public Sub DisplayAllColorsForTabs() Dim i As Long For i = 1 To 100 Worksheets.Add after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Tab.Color = i * 20000 Next i End Sub |
The *20000 is used to randomize a bit the colors on the next sheet. That’s all! 🙂