Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Row Color | Excel Discussion (Misc queries) | |||
changing the color of a cell | Excel Discussion (Misc queries) | |||
Changing color of a row | Excel Discussion (Misc queries) | |||
Changing color in color palette | Setting up and Configuration of Excel | |||
changing bg color | Excel Programming |