Objective- To color a tab of an Excel ..It is often seen that most of us like to color the tab of the excel .to distinguish between different sheets. The color code may say different meaning.


It may be for different purpose.
I have found out a solution and the way to do that.
Excel way–
1. Select the tab
2. Click on format

3. Go to Tab Color
4. Expand it and select the color.

Coding Way

Well..this code is a simple VBA code sample to do this tab color dynamically through coding.
Lets see how we can do that..

Sub Macro1()

MsgBox ThisWorkbook.Sheets.Count
For i = 1 To ThisWorkbook.Sheets.Count
Worksheets(i).Tab.ColorIndex = 10 *i
End Sub

Here I have taken the sheet count first and to give color i have picked randomly as 10*i

The important code to note here is…

Worksheets(i).Tab.ColorIndex=”Some value”

Alternatively you can also get the ColorIndex programatically


Want to do more complex programing??? like—get those sheets name whose color is red
Yes by this code you can do that as well…

Sub Macro1()
For i = 1 To ThisWorkbook.Sheets.Count
If Worksheets(i).Tab.ColorIndex = 10 Then
MsgBox Worksheets(i).name
End If
End Sub

Hope this has helped you..let me know if you need any more info!!!
Do comment or mail me if you need any more help on this. 

The good news is this is applicable while coding through QTP also.

Don't miss out!
Subscribe To Newsletter

Receive top technical news, lesson ideas, travel tips and more!

Invalid email address
Give it a try. You can unsubscribe at any time.

I am the founder and owner of the blog - TechTravelHub.com, always love to share knowledge on test automation,tools,techniques and tips.I am a passionate coder of Java and VBScript.I also publish articles on Travel ideas and great honeymoon destinations.Apart from these, I am a gear-head,love to drive across India. I have shared lots of articles here on How to travel several parts of India.Customization of cars aka car modification is my another hobby.Get in touch with me on [email protected]

Write A Comment