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


vba read excel filesum if formula in excelexcel insert columnsexcel call macroclose userform in excel vbaunprotect excel with passwordexcel formulas leftlistbox multiselect vbasorting objects worksheetssql query for excelexcel 2010 identify duplicatespaste special formulaunlock excel macrodelete module vbaexcel vba status bar messagevlookup practicevisual basic codingsmacro remove duplicatescreate an array in vbavba call macroproject dashboard excel templatevba activeworkbookgetusername vbaexcel formula example sheetaccess vba adodb recordsethow to merge and center on excelloop in excel macroarray excel vbavba create graphunlock excel macrovba closevlookup syntax in excelwriting a macro in excel 2007vba export excelvba workbook objectvba ebookwhy vlookup is usedvba check file existshow to protect an excel worksheethow to create if statements in excelvba chrvba excel dialog boxhow to put countif formula in excelhow to expand rows in excelvba filedialog selecteditemsadvanced excel vbavba saveexcel vba macro buttonunhide columns vbaactivesheet vbasubstitute vbalearn pivot tables in excel 2007how to learn excel vba programmingexcel vb programmingexcel 2007 autofit row heighthow to do multiple if statements in excelpivot table tutorial excel 2013unprotect excel sheet 2007macro commands in excelhow to unhide in excellearn vba macrosvba handbook pdfmacro to combine worksheetsmicro security in excel 2007enable developer tab exceldml ddl dcl and tcl in sqlvlookup formula in excel 2007excel power pivot tutorialcompile excel vbaexcel delete duplicate linesdata manipulation language dmladvanced filtering excel 2010macro excel for dummieswriting vlookup formulas in excelpmo templates excelfso.createfoldercollection in vbafor loops in excelexcel templates checklistexcel linked cellsno of rows in excel 2007excel consolidate sheets into onevlookup in excel step by stepvba classes