Macro change sheet tab color and name

[Gene Augustin]

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]

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

 

[Sheeloo]

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

 

[Shane Devenshire]

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]

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.

 

[Dave Peterson]

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

What version of excel are you using?

 

[Gene Augustin]

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

 

[Dave Peterson]

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

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