Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I have a workbook with a variable number of tabs depending on the data imported.

    The tabs are in order "alphabetically" and are something like:

    100-ABC XX
    100-1 DDD
    100-2 ABC YY
    110-YYY XX
    110-1
    123
    123-1 AAA
    157 BBB
    INDEX

    I want to color code the tabs based on the leading 2 numerical digits of the tab name, if possible.
    The last tab does not have numerical leading digits and is already color coded by me in a macro.

    Grouping by color: I need to add to the macro something that will take all of the tabs that begin with "10" and color code them one color; all of them that begin with "11" and color code them another color, and ALL of the others, regardless of the leading two digits, except for the last tab, coded yet a third color.

    Is this ridiculous or can it be done diagrammatically in a macro? The color coding is important as a UI for this application.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try the following macro:

    Code:
    Sub ColorTabs()
      Dim i As Integer
      Dim wsh As Worksheet
      Dim strLeft As String
      For i = 1 To ActiveWorkbook.Worksheets.Count - 1
    	Set wsh = ActiveWorkbook.Worksheets(i)
    	strLeft = Left(wsh.Name, 2)
    	Select Case strLeft
    	  Case "10"
    		wsh.Tab.ColorIndex = 3 ' red
    	  Case "11"
    		wsh.Tab.ColorIndex = 4 ' green
    	  Case Else
    		wsh.Tab.ColorIndex = 5 ' blue
    	End Select
      Next i
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Fantastic, as usual! Thanks, Hans.

    [quote name='HansV' post='774698' date='11-May-2009 14:29']Try the following macro:

    Code:
    Sub ColorTabs()
      Dim i As Integer
      Dim wsh As Worksheet
      Dim strLeft As String
      For i = 1 To ActiveWorkbook.Worksheets.Count - 1
    	Set wsh = ActiveWorkbook.Worksheets(i)
    	strLeft = Left(wsh.Name, 2)
    	Select Case strLeft
    	  Case "10"
    		wsh.Tab.ColorIndex = 3 ' red
    	  Case "11"
    		wsh.Tab.ColorIndex = 4 ' green
    	  Case Else
    		wsh.Tab.ColorIndex = 5 ' blue
    	End Select
      Next i
    End Sub
    [/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •