[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
