Description:

When we prepare a report or a dashboard it is good idea to change the color of 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.

Change the Color of Sheet Tabs in Excel VBA – Solution

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

Change the Color of Sheet Tabs in Excel VBA – Examples

Following Examples will show you how to change the Color of Sheet tabs using Excel VBA. In the following Example I am changing the Sheet2 tabs color to Red.

Code
Sub sbColorASheetTab()


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

End Sub

Instructions:
  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 should see the Sheet2 tab in Red color

Change the Color of All Sheet Tabs in Excel VBA – Examples

You can loop through the all sheets of the workbook by using Worksheets collection of the workbook. And create a variable to hold the colorIndex and assign to each sheet.
See the following example code to know how to color all sheet tabs of a workbook. In this example I am coloring each Sheet tab in Unique color.

Code
Sub sbColorAllSheetTab()
'Declaration
Dim iCntr, sht

'This will hold the colorIndex number
iCntr = 2

'looping throgh the all the sheets of the workbook
For Each sht In ThisWorkbook.Worksheets
    iCntr = iCntr + 1
    
    'Applying the colors to Sheet tabs
    sht.Tab.ColorIndex = iCntr
Next

End Sub

Instructions:
  1. Open an excel workbook
  2. Add worksheets (you can 10-50 worksheets)
  3. Press Alt+F11 to open VBA Editor
  4. Insert a new module from Insert menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to see the output
  8. You should see all the sheet tabs are colored as shown below

Change the Color of Sheet Tabs in Excel VBA

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: February 2nd, 2013Categories: Excel VBATags: ,

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

2 Comments

  1. Stephan May 20, 2019 at 11:36 PM

    What if I have more than 50 Tabs?

    • PNRao July 4, 2019 at 6:17 PM

      sbColorAllSheetTab macro can handle all the tabs in the workbook.

Leave A Comment