What is the Usage of sheet color in Excel?

When we prepare a report or a dashboard it is easy to identify or analyze reports with a change of color sheet tabs. Analysts generally give the same color to the tabs which are related to same function. 

For example if you are preparing a dashboard for all the departments in an organization. All the worksheet tabs related finance can be highlighted in red, HR can be in Blue, etc. so with this report looks good and is easy to understand.

How to use VBA Code to Color Excel Worksheet or tab?

We can change the Worksheet tab colors by setting the Tab.ColorIndex property using Excel VBA.

This Example will show you how to change the Color of Sheet tabs using Excel VBA. In the following Example we are changing the Sheet2 tabs color to Red. Lets have a look

VBA Code To Change Tab Color

Sub redColorSheetTab()

Sheets("Sheet2").Tab.ColorIndex = 3 
'3=Red, 4=green, 5=blue,6=yellow etc.

End Sub

Steps to use this VBA Code

  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to see the output
  7. You can see the Sheet2 tab in Red color

Another method to Change Tab Color using Excel VBA

Here is another help code for programmers to change the color of Excel sheet tab. I have shown another way to achieve this:

Step 1
  • Using standard colors like Green, Blue, Red, White etc.
VBA Code to change sheet color
Change the Color of Sheet Tabs in Excel VBA – Example
Step 2
  • Using RGB (Red, Green Blue) function to set a specific color
'This function changes the tab color of a sheet
Sub ChangeSheetTabColor()
    '
    'Option 1 - using standard colors vbGreen, vbRed, vbBlack, vbYellow, vbBlue, vbWhite etc.
    Sheet1.Tab.Color = vbGreen
    '
    'Option 2 - using RGB colors
    Sheet1.Tab.Color = RGB(117, 117, 117)
    '
End Sub

You may have noticed that in Option 2, I have used RGB function to change the sheet tab color. You may read this post to know more about how to get RGB codes of a color.

Other Examples of Coloring Excel Tabs using VBA

VBA Code To Color The ActiveSheet

If you need to change the color of the tab you are currently viewing, you can use the following VBA macro code along with your desired RGB color code:

Sub ChangeTabColor()

    'Objective: Change Selected Tab To Specific Color
    ActiveSheet.Tab.Color = RGB(25, 25, 25)

End Sub

VBA Code To Remove Color From All Sheets

If you need to write a VBA loop to ensure all worksheet tabs have their color removed, you can use a macro similar to the below code:

Sub ClearAllTabColor()

    'PURPOSE: Remove Tab Color from all Sheets
    Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Worksheets
        sht.Tab.Color = xlNone
    Next sht

End Sub

Download Practice File for Free

To help you practice this code, we have made this code available through practice file. Click on the below link to download the practice file.

Some other VBA Codes which might be Helpful

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *