# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] 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

----------


## Kaak

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

----------


## Norman Jones

Hi Minitman,

Try:

Activesheet.Tab.ColorIndex = 53


---
Regards,
Norman



"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:82oje15sck343p9emujhloh0hmdpb7t6v9@4ax.com...
> 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

----------


## 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



On Fri, 29 Jul 2005 09:19:03 +0100, "Norman Jones"
<normanjones@whereforartthou.com> wrote:

>Hi Minitman,
>
>Try:
>
>    Activesheet.Tab.ColorIndex = 53
>
>
>---
>Regards,
>Norman
>
>
>
>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>news:82oje15sck343p9emujhloh0hmdpb7t6v9@4ax.com...
>> 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,

What version of Excel are you using this code with?

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

---
Regards,
Norman



"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:02qje1dom9q0j27inmg99h258rkq14ekm7@4ax.com...
> 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"
> <normanjones@whereforartthou.com> wrote:
>
>>Hi Minitman,
>>
>>Try:
>>
>>    Activesheet.Tab.ColorIndex = 53
>>
>>
>>---
>>Regards,
>>Norman
>>
>>
>>
>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>news:82oje15sck343p9emujhloh0hmdpb7t6v9@4ax.com...
>>> 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
>>
>

----------


## Bob Phillips

It only applies to XP or above.

--

HTH

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


"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:02qje1dom9q0j27inmg99h258rkq14ekm7@4ax.com...
> 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"
> <normanjones@whereforartthou.com> wrote:
>
> >Hi Minitman,
> >
> >Try:
> >
> >    Activesheet.Tab.ColorIndex = 53
> >
> >
> >---
> >Regards,
> >Norman
> >
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:82oje15sck343p9emujhloh0hmdpb7t6v9@4ax.com...
> >> 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
> >
>

----------


## 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


On Fri, 29 Jul 2005 10:04:03 +0100, "Norman Jones"
<normanjones@whereforartthou.com> 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" <exreply@i-m-pNOSPAM.net> wrote in message
>news:02qje1dom9q0j27inmg99h258rkq14ekm7@4ax.com...
>> 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"
>> <normanjones@whereforartthou.com> wrote:
>>
>>>Hi Minitman,
>>>
>>>Try:
>>>
>>>    Activesheet.Tab.ColorIndex = 53
>>>
>>>
>>>---
>>>Regards,
>>>Norman
>>>
>>>
>>>
>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>news:82oje15sck343p9emujhloh0hmdpb7t6v9@4ax.com...
>>>> 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
>>>
>>
>

----------


## 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


On Fri, 29 Jul 2005 03:33:42 -0500, Kaak
<Kaak.1sxbml_1122627954.0276@excelforum-nospam.com> wrote:

>
>ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorIndex = 53

----------


## 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



On Fri, 29 Jul 2005 10:52:07 +0100, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>It only applies to XP or above.

----------


## Tom Ogilvy

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" <exreply@i-m-pNOSPAM.net> wrote in message
news:ojoke1hh2qijpk1n0mdlbp066kgtutpmfr@4ax.com...
> 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
>
>
> On Fri, 29 Jul 2005 03:33:42 -0500, Kaak
> <Kaak.1sxbml_1122627954.0276@excelforum-nospam.com> wrote:
>
> >
> >ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorIndex = 53
>

----------


## 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

On Fri, 29 Jul 2005 14:52:41 -0400, "Tom Ogilvy" <twogilvy@msn.com>
wrote:

>Activesheet.Tab.ColorIndex = 53
>
>worked fine for me, but you probably won't really notice the change until
>you select another sheet.

----------


## wschol

Hi,

I need help with this:

What I would like to do is to have the color of a tab change as the dependant upon the value of one or more cells.

I'm not so good with VBA, but I can hardly understand it, so it would be great if you could work out a code for me.

Thanks,
Willem

----------


## royUK

wschoi

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------

