Changing Tab Color With VBA

[Minitman]

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

 

[Norman Jones]

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53

 

[Minitman]

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

 

[Kaak]

ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorIndex = 5

 

[Norman Jones]

Hi Minitman,

What version of Excel are you using this code with?

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

 

[Bob Phillips]

It only applies to XP or above.

--

HTH

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

 

[Minitman]

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

 

[Minitman]

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.ColorIndex = 53

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

TIA

-Minitman

 

[Minitman]

Hey Bob,

Sorry for not listing my software.

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

Thanks for the reply.

-Minitman

 

[Tom Ogilvy]

Activesheet.Tab.ColorIndex = 53

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

 

[Minitman]

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