I have a macro that I want to change the color of the tabs ( I think it is
tab in english, I am referring to the small part of the worksheet that
displays the name etc..). When writing the code I want to select a number of
tabs and then change their color instead of writing code for every one of
them. However I dont know how to write it.

Private Sub Workbook_Open()
Sheets(Array("Tracking Error", "Mod Dur", "Indata")).Select
With Selection.Tab
.ColorIndex = 4
End With

My code does not work. I do not want to record a macro since I want to
change it later...Please help me!

I couldn't tell you how to code that but I do know that you can normally
still edit macros after recording them. And it can be a really good way of
learning how to use VBA for Excel.

"Fabrizio" wrote:

I have a macro that I want to change the color of the tabs ( I think it is
tab in english, I am referring to the small part of the worksheet that
displays the name etc..). When writing the code I want to select a number of
tabs and then change their color instead of writing code for every one of
them. However I dont know how to write it.

Private Sub Workbook_Open()
Sheets(Array("Tracking Error", "Mod Dur", "Indata")).Select
With Selection.Tab
.ColorIndex = 4
End With

My code does not work. I do not want to record a macro since I want to
change it later...Please help me!

Hi Fabrizio,

Unfortunately you can set tab color only one by one:
Private Sub Workbook_Open()
Set sharr = Sheets(Array("Tracking Error", "Mod Dur", "Indata"))
For Each wsh In sharr
wsh.Tab.ColorIndex = 4
Next wsh
End Sub

Regards,
Stefi

€žFabrizio€� ezt Ã*rta:

I have a macro that I want to change the color of the tabs ( I think it is
tab in english, I am referring to the small part of the worksheet that
displays the name etc..). When writing the code I want to select a number of
tabs and then change their color instead of writing code for every one of
them. However I dont know how to write it.

Private Sub Workbook_Open()
Sheets(Array("Tracking Error", "Mod Dur", "Indata")).Select
With Selection.Tab
.ColorIndex = 4
End With

My code does not work. I do not want to record a macro since I want to
change it later...Please help me!

Damon is right, I went into a work book and recorded a macro doing what you
want to do and then wen in to edit. The code you need is as follows:

Sheets("Sheet1").Select
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 36
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"Damon" wrote:

I couldn't tell you how to code that but I do know that you can normally
still edit macros after recording them. And it can be a really good way of
learning how to use VBA for Excel.

"Fabrizio" wrote:

I have a macro that I want to change the color of the tabs ( I think it is
tab in english, I am referring to the small part of the worksheet that
displays the name etc..). When writing the code I want to select a number of
tabs and then change their color instead of writing code for every one of
them. However I dont know how to write it.

Private Sub Workbook_Open()
Sheets(Array("Tracking Error", "Mod Dur", "Indata")).Select
With Selection.Tab
.ColorIndex = 4
End With

My code does not work. I do not want to record a macro since I want to
change it later...Please help me!

As previously given

Dim sh As Object

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
For Each sh In ActiveWindow.SelectedSheets
sh.Tab.ColorIndex = 3
Next sh


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Fabrizio" wrote in message
...
I have a macro that I want to change the color of the tabs ( I think it is
tab in english, I am referring to the small part of the worksheet that
displays the name etc..). When writing the code I want to select a number
of
tabs and then change their color instead of writing code for every one of
them. However I dont know how to write it.

Private Sub Workbook_Open()
Sheets(Array("Tracking Error", "Mod Dur", "Indata")).Select
With Selection.Tab
.ColorIndex = 4
End With

My code does not work. I do not want to record a macro since I want to
change it later...Please help me!