I found two posts about colouring tabs in this ng but they're specific
to colouring tabs according to certain condition, i.e., if no data
exists, those tabs become red, etc. My financial workbook doesn't need
anything like that; it just needs to have alternating colours at all
times. But rather than continuing to do this manually, was hoping
there was a script I put in the book that updates the tabs esp. after
I add new sheets.

The alternating colours that I chose for this workbook are that sort
of yellow-orange colour, followed by brown, alternating till the end.

Thanks! D

Something like this should work. Just change the colors to suit your taste.


Option Explicit

Sub ColorTabs()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets

If wks.Index Mod 2 <> 0 Then
wks.Tab.ColorIndex = 52
Else
wks.Tab.ColorIndex = 26
End If

Next

End Sub

I should have added, if after you've got the tabs setup, you want it to
update whenever you add a sheet, you can put the same procedure in a workbook
module as a New_Sheet event.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets

If wks.Index Mod 2 <> 0 Then
wks.Tab.ColorIndex = 52
Else
wks.Tab.ColorIndex = 26
End If

Next
End Sub

[Longish post follows.]

All of it is super fantastic! Once you got me on the right track, I
was able to search around to add an extra step that gave the 1st and
2nd tabs their own colours. So the manually-invoked macro now looks
like this:

***************************************************************************
Option Explicit
Sub TABS_Alternate_Colour()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If wks.Index Mod 2 <> 0 Then
wks.Tab.ColorIndex = 30
Else
wks.Tab.ColorIndex = 40
End If
Next

Worksheets("General Ledger").Tab.ColorIndex = 50
Worksheets("PA").Tab.ColorIndex = 24

End Sub
***************************************************************************

Though I then changed the worksheet names to generic ones so that I
didn't have to worry if anyone changed those tab names. They'll
always be there in their same places, but the names might change. So
changed them to Worksheets(1) and Worksheets(2):

***************************************************************************
Option Explicit
Sub TABS_Alternate_Colour()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If wks.Index Mod 2 <> 0 Then
wks.Tab.ColorIndex = 30
Else
wks.Tab.ColorIndex = 40
End If
Next

Worksheets(1).Tab.ColorIndex = 50
Worksheets(2).Tab.ColorIndex = 24

End Sub
***************************************************************************

Regarding having a "new event" macro that changes the tab colours when
a new sheet is added, that's a super idea. I modified your original
to this, also colouring the first two tabs to 50 and 24 (script to be
located in the "ThisWorkbook" rather than in regular script modules):

***************************************************************************
Private Sub Workbook_NewSheet(ByVal Sh As Object)
' Every time a new worksheet is added, Excel updates the colours
autoMAGICALLY!!
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If wks.Index Mod 2 <> 0 Then
wks.Tab.ColorIndex = 30
Else
wks.Tab.ColorIndex = 40
End If
Next

Worksheets(1).Tab.ColorIndex = 50
Worksheets(2).Tab.ColorIndex = 24

End Sub

***************************************************************************

But I must admit there are two problems with this one - one might
create the sheets but these sheets need to go in certain order,
numerically by code. So after a tab sort, the colours might not be
alternating anymore and I'd have to run the "sort tabs
alphanumerically" script. This "sort tabs alphanumerically" script is
this one, btw:

************************
Public Sub Sort_WORKSHEET_TABS()

Dim iCount As Integer
Dim i As Integer
Dim j As Integer

On Error Resume Next
iCount = Sheets.Count
For i = 1 To iCount - 1
For j = i + 1 To iCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

End Sub
************************

The challenge with the above is that I haven't yet figured out how to
retain the "General Ledger" tab in first place, and the "PA" tab in
the second. Once I figure out to re-shuffle those back after the
sorting script, then I'll be able to integrate all the components
together and the whole thing will be automated:

1. Adding new tab(s) triggers all tabs get sorted automatically.
2. Then tabs 1 and 2 get put back into 1st and 2nd place.
3. Alternate tab colouring invoked, so all tabs get alternate
colours.
4. Tabs 1 and 2 get their original colours back.

3 and 4 are already incorporated into both tab colouring script, so
I'll start hunting around for what will take care of putting tabs 1
and 2 back to a designated spot.

Thanks!! As always, this group delivers supremely well!! <g> Despite
all the time I spend looking for answers and posting, etc., it saves
me so much time once solutions are found.

Thanks! D

> 2. Then tabs 1 and 2 get put back into 1st and 2nd place.

Hi. After sorting, would this work?

Sheets("General Ledger").Move Sheets(1)
Sheets("PA").Move Sheets(2)


Just something different...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
' = = = = = = = = = = = = =
' Every time a new worksheet is added,
' Excel updates the colours autoMAGICALLY!!
' = = = = = = = = = = = = =

Dim wks As Worksheet

'// Odd = 30, Even = 40
For Each wks In ThisWorkbook.Worksheets
wks.Tab.ColorIndex = 40 - 10 * Sgn(wks.Index Mod 2)
Next

Worksheets(1).Tab.ColorIndex = 50
Worksheets(2).Tab.ColorIndex = 24
End Sub

= = = = = = = = =
HTH :>)
Dana DeLouis



StargateFanNotAtHome wrote:
> [Longish post follows.]
>
> All of it is super fantastic! Once you got me on the right track, I
> was able to search around to add an extra step that gave the 1st and
> 2nd tabs their own colours. So the manually-invoked macro now looks
> like this:
>
> ***************************************************************************
> Option Explicit
> Sub TABS_Alternate_Colour()
>
> Dim wks As Worksheet
>
> For Each wks In ThisWorkbook.Worksheets
> If wks.Index Mod 2 <> 0 Then
> wks.Tab.ColorIndex = 30
> Else
> wks.Tab.ColorIndex = 40
> End If
> Next
>
> Worksheets("General Ledger").Tab.ColorIndex = 50
> Worksheets("PA").Tab.ColorIndex = 24
>
> End Sub
> ***************************************************************************
>
> Though I then changed the worksheet names to generic ones so that I
> didn't have to worry if anyone changed those tab names. They'll
> always be there in their same places, but the names might change. So
> changed them to Worksheets(1) and Worksheets(2):
>
> ***************************************************************************
> Option Explicit
> Sub TABS_Alternate_Colour()
>
> Dim wks As Worksheet
>
> For Each wks In ThisWorkbook.Worksheets
> If wks.Index Mod 2 <> 0 Then
> wks.Tab.ColorIndex = 30
> Else
> wks.Tab.ColorIndex = 40
> End If
> Next
>
> Worksheets(1).Tab.ColorIndex = 50
> Worksheets(2).Tab.ColorIndex = 24
>
> End Sub
> ***************************************************************************
>
> Regarding having a "new event" macro that changes the tab colours when
> a new sheet is added, that's a super idea. I modified your original
> to this, also colouring the first two tabs to 50 and 24 (script to be
> located in the "ThisWorkbook" rather than in regular script modules):
>
> ***************************************************************************
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> ' Every time a new worksheet is added, Excel updates the colours
> autoMAGICALLY!!
> Dim wks As Worksheet
>
> For Each wks In ThisWorkbook.Worksheets
> If wks.Index Mod 2 <> 0 Then
> wks.Tab.ColorIndex = 30
> Else
> wks.Tab.ColorIndex = 40
> End If
> Next
>
> Worksheets(1).Tab.ColorIndex = 50
> Worksheets(2).Tab.ColorIndex = 24
>
> End Sub
>
> ***************************************************************************
>
> But I must admit there are two problems with this one - one might
> create the sheets but these sheets need to go in certain order,
> numerically by code. So after a tab sort, the colours might not be
> alternating anymore and I'd have to run the "sort tabs
> alphanumerically" script. This "sort tabs alphanumerically" script is
> this one, btw:
>
> ************************
> Public Sub Sort_WORKSHEET_TABS()
>
> Dim iCount As Integer
> Dim i As Integer
> Dim j As Integer
>
> On Error Resume Next
> iCount = Sheets.Count
> For i = 1 To iCount - 1
> For j = i + 1 To iCount
> If Sheets(j).Name < Sheets(i).Name Then
> Sheets(j).Move before:=Sheets(i)
> End If
> Next j
> Next i
>
> End Sub
> ************************
>
> The challenge with the above is that I haven't yet figured out how to
> retain the "General Ledger" tab in first place, and the "PA" tab in
> the second. Once I figure out to re-shuffle those back after the
> sorting script, then I'll be able to integrate all the components
> together and the whole thing will be automated:
>
> 1. Adding new tab(s) triggers all tabs get sorted automatically.
> 2. Then tabs 1 and 2 get put back into 1st and 2nd place.
> 3. Alternate tab colouring invoked, so all tabs get alternate
> colours.
> 4. Tabs 1 and 2 get their original colours back.
>
> 3 and 4 are already incorporated into both tab colouring script, so
> I'll start hunting around for what will take care of putting tabs 1
> and 2 back to a designated spot.
>
> Thanks!! As always, this group delivers supremely well!! <g> Despite
> all the time I spend looking for answers and posting, etc., it saves
> me so much time once solutions are found.
>
> Thanks! D

On Sat, 15 Aug 2009 10:54:37 -0400, Dana DeLouis
<(E-Mail Removed)> wrote:

> > 2. Then tabs 1 and 2 get put back into 1st and 2nd place.
>
>Hi. After sorting, would this work?
>
> Sheets("General Ledger").Move Sheets(1)
> Sheets("PA").Move Sheets(2)
>
>
>Just something different...
>
>Private Sub Workbook_NewSheet(ByVal Sh As Object)
>' = = = = = = = = = = = = =
>' Every time a new worksheet is added,
>' Excel updates the colours autoMAGICALLY!!
>' = = = = = = = = = = = = =
>
> Dim wks As Worksheet
>
>'// Odd = 30, Even = 40
> For Each wks In ThisWorkbook.Worksheets
> wks.Tab.ColorIndex = 40 - 10 * Sgn(wks.Index Mod 2)
> Next
>
> Worksheets(1).Tab.ColorIndex = 50
> Worksheets(2).Tab.ColorIndex = 24
>End Sub
>
>= = = = = = = = =
>HTH :>)
>Dana DeLouis

[snip]

Interesting. Will give it a try. Thanks! D