Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 8th 09, 01:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 36
Default 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


  #2   Report Post  
Old March 8th 09, 02:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #3   Report Post  
Old March 8th 09, 02:31 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default 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


  #4   Report Post  
Old March 8th 09, 05:34 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 857
Default 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


  #5   Report Post  
Old March 8th 09, 06:07 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 36
Default 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





  #6   Report Post  
Old March 8th 09, 06:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change sheet tab name and color Gene Augustin Excel Discussion (Misc queries) 1 March 7th 09 11:33 PM
change the sheet tab color Tracy Excel Discussion (Misc queries) 1 June 8th 06 06:34 PM
Macro To Change Cell Color When Value Changes carl Excel Worksheet Functions 4 March 14th 06 08:24 PM
change sheet tab color? mark Excel Discussion (Misc queries) 2 May 26th 05 11:18 PM
macro color change Dan Excel Worksheet Functions 0 February 15th 05 09:35 PM


All times are GMT +1. The time now is 11:30 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017