change color of tab macro

[rki1966]

I want a macro that will change the color of all tabs within the workbook to one color.

The formula gives me errors:

Sub Color2()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
With Selection.Tab
.ColorIndex = 6
End With

Next ws
End Sub

 

[Zack Barresse]

Hi there,

This works for me ..

<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Color3()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets
        ws.Tab.ColorIndex = 6
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It's not working for you because the Selection object is for a range, not a worksheet. The range object doesn't have a Tab property.

 

[rki1966]

thanks that works

 

[Joe Was]

You can also do it directly or with conditional logic:

Sub tabColorToggle()

If ActiveWorkbook.Sheets("Sheet2").Tab.ColorIndex = xlNone Then
ActiveWorkbook.Sheets("Sheet2").Tab.ColorIndex = 6
Else
ActiveWorkbook.Sheets("Sheet2").Tab.ColorIndex = xlNone
End If
End Sub