Solved: Changing tab color

[austenr]

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]

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]

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]

ok thanks. Can you offer suggestions on another post I just did concerning the offset ptoperty? Thanks