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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ub" wrote:

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

Hi Dave
I have Excel 2000 version
Thanks

"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ub" wrote:

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

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub wrote:

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

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Gord Dibben" wrote:

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub wrote:

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

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

"Gord Dibben" wrote:

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub wrote:

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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ub" wrote:

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

"Gord Dibben" wrote:

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub wrote:

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

Hi Dave
Thanks for yr help.
Regards

"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ub" wrote:

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

"Gord Dibben" wrote:

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub wrote:

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

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




"ub" wrote in message
...
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

"Gord Dibben" wrote:

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub
wrote:

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

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

Gord

On Mon, 09 Apr 2007 13:08:44 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Mon, 9 Apr 2007 12:32:01 -0700, ub wrote:

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

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...#sortallsheets

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"ub" wrote in message ...
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