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

Rename & Colour Sheet Q
 

Joel, that's fantastic, works like a dream

Thanks