Macro change sheet tab color and name

[Gene Augustin]

I originally posted this in microsoft.public.excel.misc

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.

Original replies:


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


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

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

If I get rid of the color references, the sheets are added and properly
named.


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


Gene Augustin

 

[CyberTaz]

Well, since VBA isn't supported in Mac Excel 2008 & tab color isn't
supported in any Mac version of the program I'm gonna guess that you're
using a PC version & you've gotten misdirected... Note the *mac* in the
newsgroup address you can find the PC Office groups here:

http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx

Regards |:>)
Bob Jones
[MVP] Office:Mac