Color multiple Excel worksheet tabs

How to color multiple Excel worksheet tabs at the same time, using Excel and VBA methods

METHOD 1. Color multiple Excel worksheet tabs using the sheet option

EXCEL

Select multiple worksheets > Right-click on one of the selected worksheets > Tab Color > Select the tab color from Theme Colors, Standard Colors or More Colors

1. Press and hold the Shift key and select the worksheets that you want to color.
Note: in this example we are coloring three worksheets (Sheet1, Sheet2 and Sheet3).

2. Right-click on any of the selected worksheets.
3. Click Tab Color and select the tab color from Theme Colors, Standard Colors or More Colors.

Right-click on one of the selected worksheets, select Tab color and select the color you want

METHOD 2. Color multiple Excel worksheet tabs using the ribbon option

EXCEL

Select multiple worksheets > Home tab > Cells group > Format > Tab Color > Select the tab color from Theme Colors, Standard Colors or More Colors

1. Press and hold the Shift key and select the worksheets that you want to color.
Note: in this example we are coloring three worksheets (Sheet1, Sheet2 and Sheet3).

2. Select the Home tab.

3. Click Format in the Cells group.
4. Click Tab Color and select the tab color from Theme Colors, Standard Colors or More Colors.

Select Format, select Tab color and select the color that you want

METHOD 1. Color multiple Excel worksheet tabs using VBA

VBA

Sub Color_Multiple_Excel_Worksheet_Tabs()

'declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

'color three worksheets at the same time
ws1.Tab.Color = RGB(0, 255, 0)
ws2.Tab.Color = RGB(0, 255, 0)
ws3.Tab.Color = RGB(0, 255, 0)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Minimum Number of Worksheets: Have at least three worksheets in a workbook.
Worksheet Names: Have three worksheets named Sheet1, Sheet2 and Sheet3.

ADJUSTABLE PARAMETERS
Worksheets to Color: Select worksheets that you want to color by changing Sheet1, Sheet2 and Sheet3 worksheet names in the VBA code to any worksheet in the workbook.

METHOD 2. Color multiple Excel worksheet tabs from a list of colors using VBA

VBA

Sub Color_Multiple_Excel_Worksheets_Tabs()

Set ws1 = Worksheets("Parameters").Range("D2")
Set ws2 = Worksheets("Parameters").Range("D3")
Set ws3 = Worksheets("Parameters").Range("D4")

'color three worksheets at the same time
Worksheets(ws1.Value).Tab.Color = RGB(0, 255, 0)
Worksheets(ws2.Value).Tab.Color = RGB(0, 255, 0)
Worksheets(ws3.Value).Tab.Color = RGB(0, 255, 0)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Minimum Number of Worksheets: Have at least four worksheets in a workbook, including the Parameters worksheet.
Worksheet Names: Have four worksheets named Parameters, Sheet1, Sheet2 and Sheet3.
Worksheets Selection: Cells ("D2"), ("D3") and ("D4") in the Parameters worksheet need to be populated with the names of the worksheets that you want to color.

ADJUSTABLE PARAMETERS
Worksheets to Color: Select worksheets that you want to color by changing the names in cells ("D2"), ("D3") and ("D4") in the Parameters worksheet.

METHOD 3. Color multiple Excel worksheet tabs from a list of names with a For Loop using VBA

VBA

Sub Color_Multiple_Excel_Worksheet_Tabs()

On Error Resume Next
For Each colorws In ThisWorkbook.Worksheets("Parameters").Range("D2:D4")
ThisWorkbook.Worksheets(colorws.Value).Tab.Color = RGB(0, 255, 0)

End Sub

ADJUSTABLE PARAMETERS
Worksheets to Color: Select worksheets that you want to color by changing the names in cells ("D2"), ("D3") and ("D4") in the Parameters worksheet.

METHOD 4. Color multiple Excel worksheet tabs from a list of names with a For Loop using VBA

VBA

Sub Color_Multiple_Excel_Worksheet_Tabs()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Parameters")

For x = 2 To 4

On Error Resume Next

colorws = ws.Cells(x, 4).Value
Worksheets(colorws).Tab.Color = RGB(0, 255, 0)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Minimum Number of Worksheets: Have at least four worksheets in a workbook, including the Parameters worksheet.
Worksheet Names: Have four worksheets named Parameters, Sheet1, Sheet2 and Sheet3.
Minimum Number of Worksheets: Cells ("D2"), ("D3") and ("D4") in the Parameters worksheet need to be populated with the names of the worksheets that you want to color.

ADJUSTABLE PARAMETERS
Worksheet to Color: Select worksheets that you want to color by changing the names in cells ("D2"), ("D3") and ("D4") in the Parameters worksheet.

Explanation about how to color multiple worksheet tabs

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to color multiple worksheet tabs using Excel and VBA methods.

Excel Methods: Using Excel you can color multiple worksheet tabs with a ribbon or sheet option.

VBA Methods: Using VBA you can color multiple worksheet tabs by directly entering the names of the worksheets that you want to color in the VBA code or by referencing to cells that capture the names of the worksheets that you want to color. You can also use the color index number, RGB code or vb color index to nominate the color of the worksheet tab. In this example we are applying the RGB code.