Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename & Colour Sheet Q
Sub ColorTabs()
ColorBlue = 5 ColorYellow = 6 FirstDate = Sheets(1).Name 'replace undre score with slash FirstDate = Replace(FirstDate, "_", "/") ShtDate = DateValue(FirstDate) ShtDate = ShtDate + 7 FirstColor = Sheets(1).Tab.ColorIndex Select Case FirstColor Case Color_Blue: ShtColor = ColorBlue Case Color_Blue: ShtColor = ColorYellow Case Else: 'if color doesn't match yellow or blue make blue ShtColor = ColorBlue Sheets(1).Tab.ColorIndex = ColorBlue End Select For ShtCount = 2 To Sheets.Count With Sheets(ShtCount) .Name = Format(ShtDate, "DD_MM_YY") ShtDate = ShtDate + 7 Select Case ShtColor Case ColorBlue: ShtColor = ColorYellow .Tab.ColorIndex = ShtColor Case ColorYellow: ShtColor = ColorBlue .Tab.ColorIndex = ShtColor End Select End With Next ShtCount End Sub "Seanie" wrote: What code could I use to automate these two tasks:- 1) Change the Active Sheet tab colour to: Blue if the sheet immediately to the left is Yellow or Yellow if the sheet immediately to the left is Blue 2) Rename the Active Sheet to "the name of the sheet immediately to the left" + 7. The format of the sheet is dd-mm-yy, so if the sheet to the left is 17-05-09 my new Active Sheet would be 24-05-09 If (2) above is even possible Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename & Colour Sheet Q
Joel, that's fantastic, works like a dream
Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i rename a sheet if the rename tab is inactive? | Excel Worksheet Functions | |||
macro to: Add new sheet, then rename new sheet with todays date | Excel Worksheet Functions | |||
Move data to new sheet - rename sheet based on criteria ? | Excel Discussion (Misc queries) | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
Button to copy sheet, rename sheet sequencially. | Excel Programming |