Header Ads

How to Color a Tab in Excel-2007 in VBA

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
Next
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


xx=Worksheets(i).Tab.ColorIndex
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
Next
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.
Powered by Blogger.