PDA

View Full Version : Solved: Changing tab color



austenr
11-10-2005, 08:43 AM
I am trying to adapt this to chage the tab colors. The problem is that no matter what value I use for the tab color, it always sets it to black. Are there seperate color indexes for the tab colors?

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer, ws As Variant
WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
If ActiveWorkbook.Worksheets(I).Name = "Sheet1" Then
ActiveWorkbook.Worksheets(I).Tab.Color = 15
Else
If ActiveWorkbook.Worksheets(I).Name = "Sheet2" Then
ActiveWorkbook.Worksheets(I).Tab.Color = 25
End If
End If
Next I
End Sub

Also, can this be accomplished with a Select Case statement? Thanks

mvidas
11-10-2005, 09:03 AM
Hi Austenr,

You wouldn't need a select case statement here, since you only have 2 sheets you're doing this to. I can't test this, since I can't change my tab colors (xl2k), but you could use something like:Sub WorksheetLoop()
Dim I As Integer
For I = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets(I).Name = "Sheet1" Then
ActiveWorkbook.Worksheets(I).Tab.Color = 15
ElseIf ActiveWorkbook.Worksheets(I).Name = "Sheet2" Then
ActiveWorkbook.Worksheets(I).Tab.Color = 25
End If
Next I
End Sub
But you could also just use:Sub WorksheetLoop()
On Error Resume Next
Sheets("Sheet1").Tab.Color = 15
Sheets("Sheet2").Tab.Color = 25
On Error GoTo 0
End SubSeems like that would be the easier way to go.
Matt

malik641
11-10-2005, 09:46 AM
Unable to test (also running XL2K at work)..but I think all you need to do is to use ColorIndex instead of color.

...This is just a guess from what I've found online:




Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer, ws As Variant
WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
If ActiveWorkbook.Worksheets(I).Name = "Sheet1" Then
ActiveWorkbook.Worksheets(I).Tab.ColorIndex = 15
Else
If ActiveWorkbook.Worksheets(I).Name = "Sheet2" Then
ActiveWorkbook.Worksheets(I).Tab.ColorIndex = 25
End If
End If
Next I
End Sub


EDIT: Here is the site I was talking about:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlproColorIndex1_HV03076651.asp

:thumb

austenr
11-10-2005, 09:48 AM
ok thanks. Can you offer suggestions on another post I just did concerning the offset ptoperty? Thanks