Macro change sheet tab color and name
 

I have a workbook with only one sheet. I want to use a macro to change the
name of that sheet to "QIF" and its color to RED and add a second worksheet
with "IIF" name and color Yellow.

Gene Augustin

Macro change sheet tab color and name
 

Try recording a macro when you do it manually and I bet you'll end up with code
that works fine.

Gene Augustin wrote:

I have a workbook with only one sheet. I want to use a macro to change the
name of that sheet to "QIF" and its color to RED and add a second worksheet
with "IIF" name and color Yellow.

Gene Augustin

--

Dave Peterson

Macro change sheet tab color and name
 

Sub colorSheet()
Sheets(1).Name = "QIF"
Sheets("QIF").Tab.Color = 255
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Sheets("IIF").Tab.Color = 65535
End Sub

"Gene Augustin" wrote:

I have a workbook with only one sheet. I want to use a macro to change the
name of that sheet to "QIF" and its color to RED and add a second worksheet
with "IIF" name and color Yellow.

Gene Augustin

Macro change sheet tab color and name
 

Hi,

You can just record the steps I gave you in your previous post, that way you
get the colors you want.

You didn't tell us the version of Excel you are using.

2003:

Sub SheetSetup()
With ActiveSheet
.Name = "QIF"
.Tab.ColorIndex = 42
End With
Sheets.Add After:=ActiveSheet
With ActiveSheet
.Name = "IIF"
.Tab.ColorIndex = 47
End With
End Sub

2007:

Sub SheetSetup()
With ActiveSheet
.Name = "QIF"
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
Sheets.Add After:=ActiveSheet
End With
With ActiveSheet
.Name = "IIF"
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
End With
End Sub

-----

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Gene Augustin" wrote:

I have a workbook with only one sheet. I want to use a macro to change the
name of that sheet to "QIF" and its color to RED and add a second worksheet
with "IIF" name and color Yellow.

Gene Augustin

Macro change sheet tab color and name
 

From Shane Devenshire

..ActiveSheet.Tab.ColorIndex = 42

*********
From Sheeloo:

Sheets("QIF").Tab.Color = 255

********

Both codes give error:

Run-time error '438'
Object doesn't support this property or method


Is there an add-in that I should be using?
I have Analysis Toolpac and Analysis Toolpac-VBA installed.




On 3/7/09 9:31 PM, in article
, "Sheeloo" <Click above
to get my email id wrote:

Sub colorSheet()
Sheets(1).Name = "QIF"
Sheets("QIF").Tab.Color = 255
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Sheets("IIF").Tab.Color = 65535
End Sub

Macro change sheet tab color and name
 

You can only color the worksheet tabs if you're using xl2002 or higher.

What version of excel are you using?

Gene Augustin wrote:

From Shane Devenshire

.ActiveSheet.Tab.ColorIndex = 42

*********
From Sheeloo:

Sheets("QIF").Tab.Color = 255

********

Both codes give error:

Run-time error '438'
Object doesn't support this property or method

Is there an add-in that I should be using?
I have Analysis Toolpac and Analysis Toolpac-VBA installed.

On 3/7/09 9:31 PM, in article
, "Sheeloo" <Click above
to get my email id wrote:

Sub colorSheet()
Sheets(1).Name = "QIF"
Sheets("QIF").Tab.Color = 255
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Sheets("IIF").Tab.Color = 65535
End Sub

--

Dave Peterson

Macro change sheet tab color and name
 

Office 2004, Excel 2004, Version 11.5.3, MAC Powerbook G4, OS 10.5.6



On 3/8/09 2:47 PM, in article , "Dave
Peterson" wrote:

You can only color the worksheet tabs if you're using xl2002 or higher.

What version of excel are you using?

Gene Augustin wrote:

From Shane Devenshire

.ActiveSheet.Tab.ColorIndex = 42

*********
From Sheeloo:

Sheets("QIF").Tab.Color = 255

********

Both codes give error:

Run-time error '438'
Object doesn't support this property or method

Is there an add-in that I should be using?
I have Analysis Toolpac and Analysis Toolpac-VBA installed.

On 3/7/09 9:31 PM, in article
, "Sheeloo" <Click above
to get my email id wrote:

Sub colorSheet()
Sheets(1).Name = "QIF"
Sheets("QIF").Tab.Color = 255
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Sheets("IIF").Tab.Color = 65535
End Sub

Macro change sheet tab color and name
 

I don't know what features were added in what versions for Macs.

You may want to post your question he
news://msnews.microsoft.com/microsof...c.office.excel
if you don't get a good response.

Gene Augustin wrote:

Office 2004, Excel 2004, Version 11.5.3, MAC Powerbook G4, OS 10.5.6

On 3/8/09 2:47 PM, in article , "Dave
Peterson" wrote:

You can only color the worksheet tabs if you're using xl2002 or higher.

What version of excel are you using?

Gene Augustin wrote:

From Shane Devenshire

.ActiveSheet.Tab.ColorIndex = 42

*********
From Sheeloo:

Sheets("QIF").Tab.Color = 255

********

Both codes give error:

Run-time error '438'
Object doesn't support this property or method

Is there an add-in that I should be using?
I have Analysis Toolpac and Analysis Toolpac-VBA installed.

On 3/7/09 9:31 PM, in article
, "Sheeloo" <Click above
to get my email id wrote:

Sub colorSheet()
Sheets(1).Name = "QIF"
Sheets("QIF").Tab.Color = 255
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Sheets("IIF").Tab.Color = 65535
End Sub

--

Dave Peterson