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


excel macro casehow to use the countif function in excel 2013while loops in vbaremove a hyperlink in excelmacro excel 2007 tutorialuserform in excel 2007vba downloadsusing macros in excelsas code for beginnerspmo interview questions and answers pdfsql commands with syntax and examples pdfhow to create bar chart in excel 2007excel 2007 remove duplicatesvba scripting filesystemobjectautofit excel 2010excell if statementsdeclaring arrays in vbavba word rangeexcel cell vbahow to merge columns in excelvb database connectivity with accessms access vba export to excelshot keys of excelexcel clear cellvba chart titleexample for vlookupfind duplicates in excel listhow do you unhide columns in excelhow to remove duplicates in excel 2010excel macro file extensionvba code to open folderdcl languagevba close filelock worksheet excelunprotect excel sheet macroexcel vba message boxsql quiz questions and answers pdfvbscript examples programshow to use iserror in excelsimple excel exercises for beginners3d excel chartexcel inputboxmerge multiple worksheetsvba excel listboxchange the width of column in excelwhat is a pivot chart in excelwhat is advanced filter in exceloptimisation exceldeduplication in excelvba saveasvba join arrayvba countaifs statement excelautofit on exceluser defined function vbavba usesvba call functionvba activecellexcel insert a columnexcel run macroisblank formuladashboards excelvba instr examplevba open excel filehow to change xml to excelsumif excellaccess vba run macrohow to turn on developer tab in excel 2010vba loopsunlock excel documentdashboard excel templateexcel vba macros exampleshow to hide formula in excel without protecting sheetclose workbook without savingautosave excel 2007excel worksheet activatemessagebox function