Changing Tab Color With VBA
 

Greetings,

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

Anyone have an idea as to how to charge it for the current sheet?

As always TIA

-Minitman

Changing Tab Color With VBA
 

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53


---
Regards,
Norman



"Minitman" wrote in message
...
Greetings,

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

Anyone have an idea as to how to charge it for the current sheet?

As always TIA

-Minitman

Changing Tab Color With VBA
 

Hey Norman,

Thanks for the quick reply

Here is the macro with your code snippet:

Cells.Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Tab.ColorIndex = 53
Range("B2").Select

It does not change the tab. What am I doing wrong?

-Minitman



On Fri, 29 Jul 2005 09:19:03 +0100, "Norman Jones"
wrote:

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53


---
Regards,
Norman



"Minitman" wrote in message
.. .
Greetings,

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

Anyone have an idea as to how to charge it for the current sheet?

As always TIA

-Minitman

Changing Tab Color With VBA
 

ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorI ndex = 5

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=39121

Changing Tab Color With VBA
 

Hi Minitman,

What version of Excel are you using this code with?

What happens if you change the colorindex value (to,say,6)?

---
Regards,
Norman



"Minitman" wrote in message
...
Hey Norman,

Thanks for the quick reply

Here is the macro with your code snippet:

Cells.Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Tab.ColorIndex = 53
Range("B2").Select

It does not change the tab. What am I doing wrong?

-Minitman



On Fri, 29 Jul 2005 09:19:03 +0100, "Norman Jones"
wrote:

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53


---
Regards,
Norman



"Minitman" wrote in message
. ..
Greetings,

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

Anyone have an idea as to how to charge it for the current sheet?

As always TIA

-Minitman

Changing Tab Color With VBA
 

It only applies to XP or above.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
...
Hey Norman,

Thanks for the quick reply

Here is the macro with your code snippet:

Cells.Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Tab.ColorIndex = 53
Range("B2").Select

It does not change the tab. What am I doing wrong?

-Minitman



On Fri, 29 Jul 2005 09:19:03 +0100, "Norman Jones"
wrote:

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53


---
Regards,
Norman



"Minitman" wrote in message
.. .
Greetings,

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

Anyone have an idea as to how to charge it for the current sheet?

As always TIA

-Minitman

Changing Tab Color With VBA
 

Hey Norman,

Sorry, I forgot to mention I'm running Excel from Office 2003 on an XP
Pro box.

I can change the tab color manually. I believe 6 = yellow.

-Minitman


On Fri, 29 Jul 2005 10:04:03 +0100, "Norman Jones"
wrote:

Hi Minitman,

What version of Excel are you using this code with?

What happens if you change the colorindex value (to,say,6)?

---
Regards,
Norman



"Minitman" wrote in message
.. .
Hey Norman,

Thanks for the quick reply

Here is the macro with your code snippet:

Cells.Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Tab.ColorIndex = 53
Range("B2").Select

It does not change the tab. What am I doing wrong?

-Minitman



On Fri, 29 Jul 2005 09:19:03 +0100, "Norman Jones"
wrote:

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53


---
Regards,
Norman



"Minitman" wrote in message
...
Greetings,

I am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet

Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53

Anyone have an idea as to how to charge it for the current sheet?

As always TIA

-Minitman

Changing Tab Color With VBA
 

Hey Kaak,

Thanks for the reply, it looks like it should work

But unfortunately, I just tried it and it did not work. It gave me an
idea. though.

I went back to the macro that I recorded:

Sheets("Jul-94").Select
ActiveWorkbook.Sheets("Jul-94").Tab.ColorIndex = 53

And change it to this:

Sheets(ActiveSheet.Name).Select
ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorI ndex = 53

Still doesn't change the tab color. Any other possibilities?

TIA

-Minitman


On Fri, 29 Jul 2005 03:33:42 -0500, Kaak
wrote:


ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.Color Index = 53

Changing Tab Color With VBA
 

Hey Bob,

Sorry for not listing my software.

I am running Excel from Office 2003 in an XP box.

Thanks for the reply.

-Minitman



On Fri, 29 Jul 2005 10:52:07 +0100, "Bob Phillips"
wrote:

It only applies to XP or above.

Changing Tab Color With VBA
 

Activesheet.Tab.ColorIndex = 53

worked fine for me, but you probably won't really notice the change until
you select another sheet.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
Hey Kaak,

Thanks for the reply, it looks like it should work

But unfortunately, I just tried it and it did not work. It gave me an
idea. though.

I went back to the macro that I recorded:

Sheets("Jul-94").Select
ActiveWorkbook.Sheets("Jul-94").Tab.ColorIndex = 53

And change it to this:

Sheets(ActiveSheet.Name).Select
ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorI ndex = 53

Still doesn't change the tab color. Any other possibilities?

TIA

-Minitman


On Fri, 29 Jul 2005 03:33:42 -0500, Kaak
wrote:


ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.Color Index = 53

Changing Tab Color With VBA
 

Hey Tom,

Not sure what happened earlier, your code works great and now so does
the code from Kaak. I think I must have gremlins in my machine.

Thanks for the help.

-Minitman

On Fri, 29 Jul 2005 14:52:41 -0400, "Tom Ogilvy"
wrote:

Activesheet.Tab.ColorIndex = 53

worked fine for me, but you probably won't really notice the change until
you select another sheet.