Worksheets Tabs

[Guest]

Hi
I want to change the color of the worksheet tabs like different tab color
for different sheet
Is there any way i can do this by writing a VBA code.
Thanks

 

[Guest]

If you're using XL XP or later, just right-click the tab to select a color.
If you're using an earlier version of Excel, you can't color the tabs.

Dave

 

[Guest]

Hi Dave
I have Excel 2000 version
Thanks

If you're using XL XP or later, just right-click the tab to select a color.
If you're using an earlier version of Excel, you can't color the tabs.

Dave

 

[Gord Dibben]

Private Sub Color_Tabs()
Dim i As Integer
Dim ws As Worksheet
For i = 1 To Worksheets.Count
Set ws = Worksheets(i)
ws.Tab.ColorIndex = i
Next i
End Sub

Note: you are limited to the 56 colors Excel's palette provides.


Gord Dibben MS Excel MVP

 

[Guest]

The OP says he has XL 2000 so this macro wouldn't work.

Dave

 

[Guest]

Hi
The code editor gives error on line
ws.tab.colorindex = i
The error is complie erroe
"Method or data member not found"
Please advise

 

[Guest]

The macro does not work because you have Excel 2000. As indicated
previously, it is impossible to color the tabs in Excel 2000, whether by a
built-in feature or else via macros. It simply cannot be done.

Dave

 

[Guest]

Hi Dave
Thanks for yr help.
Regards

The macro does not work because you have Excel 2000. As indicated
previously, it is impossible to color the tabs in Excel 2000, whether by a
built-in feature or else via macros. It simply cannot be done.

Dave

 

[Conan Kelly]

ub,

You mentioned earlier that you are using XL 2000. You can't color sheet
tabs in 2000, not even with code.

You have to be using XL 2002 (XP) or later in order to color sheet tabs,
whether it be manual or with code.

Sorry,

Conan Kelly

 

[Gord Dibben]

Now that I see you have Excel 2000, ignore this post.

Gord

 

[David McRitchie]

As an alternative for Excel 2000 -- no coloring of tabs.

I believe the normal reason to color tabs is to provide an organization to them. You can sort sheet tabs with a macro. You can
enhance your sorted arrangement by preceding the sheet tab with some less conspicuous small letters prefixes. i.e.
k.FunctKeys, k.ShortCutKeys
If working with dates for sheetnames, spell the year out and place it first
2002-10, 2002-11, 2002-12, 2003-01
sort sheet tabs into alphabetical order in http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets