Change the Color of Sheet Tabs in Excel VBA

Home/Excel VBA/Change the Color of Sheet Tabs in Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

LIMITED TIME OFFER
By |February 2nd, 2013|Excel VBA|0 Comments

About the Author:

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.

Leave A Comment


Related pages


msg vbalearn vlookup in excel 2007excel worksheet name in cellexcel delete empty cellsworkbook close vbavba update sqlsyntax in excelwhat is a stock chart in exceltutorial vba excelactivate sheet vbaexcel macro save fileexcel lookup tutorialpivot table examples excel 2010excel vba nested ifado microsoftexcel sorting rowsexcel vba return arrayvba shortcut keyshow to create a dashboard in excel 2007find duplicates in an excel columnexcel vba option explicitexcel auto sorttutorial macros excelcombobox multiselectworkbook save vbapivot table vba excelexcel vba format cellseriescollection vbaexcel macro active sheetexcel formulas shortcut keys 2007excel concatanatehow do you hide rows in excelvba choosevba opendatabaseddl commands in sqlexcel 2007 vba editorwhat is ddl dml and dcl in sqldelete blank rows excelhow to build an excel macrovlookup formula in excel with exampleexcel formulas and functions for dummiesdata definition language ddl commandwhat is a vlookup function in excelduplicate finder in excelexcel 2010 remove blank rowsisnumber excelunhide worksheetsexcel chartingunhide all tabs in excelhow to consolidate two excel spreadsheets into oneconversion of xml file to excelsql dml ddl dclunprotect excel workbook onlineexcel task scheduler templateindex formula in excel 2007excel col_index_numhow to use the vlookup functionexcel vba made easyhow to use the trim function in excelvba instr examplepivot table charts excel 2010sort highest to lowest excelhow to remove empty rows from excelexcel vba syntaxexcel 2013 pivot table calculated fieldexcel case statementexcel hlookup functionhow do you unshare a workbookmeeting minutes template word 2010what is hlookup in excel with examplecreating a pivot table in excel 2007texfileexcel presentation templatesvba excel tableshading cells in excelvba macros examples