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


create userform in excelclient tracking excel templatesample excel dashboard templatesexcel remove duplicates from 2 columnsadvanced excel pivot tablesvba code to transfer data from one sheet to anotherexcel keystrokesdefine function in vbavba connection string to access databasechange a row to a column in excelvba integer data typeexcel hide columns based on cell valuehow to find duplicates in excel 2007undo macro excelexcel protected worksheetvlookup trueexcel beginners tutorialexcel duplicate cellsvlookup two worksheetsexcel copy macrorunning macros in excelexcel 2007 vba pdfvba averagevlookup case sensitivemicrosoft office estimate templatehow to unhide rows in excel 2013project management spreadsheet excelhow to unlock a password protected excel workbookgantt project planner excelsimple vba code for excelinsert checkbox in excelvba range rowsuse of vlookup and hlookup in excelcommands of ddlpowerpoint vba referencehow to build a macro in excelvba end loopconcatenate excel formulasunderstanding excel formulas and functionsrept functionexcel query sqlexcel 2013 find duplicatesvba new lineactivesheet range autofilterclear cell contents vbaexcel 2013 data validationexcel formulas color codingvba insertexcel test for nulledit macros in excelhow to unlock protected excel sheet without passwordvba unprotect all sheetsexcel vba copy to clipboardtimeline examples in excelhow to protect cells in a worksheetvlookup function excel 2007add drop down in excel 2007adding multiple columns in excelexcel spreadsheet dashboard templateshow do you hide columns in excelarrays in vbasystem date in vbaagenda template excelexcel vba printrca template wordexcel shotcutmacro excel vbaunprotect excel sheet without passwordhow to create excel macrohow to extract duplicates in excelunhide column a excel 2007vba save worksheetexcel macros for dummiesidentify duplicate values in excelhow to protect workbook in excel 2007excel bar graph templates