Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Seattle, WA - USA
    Thanked 0 Times in 0 Posts

    Active sheet tab color

    Hi all,

    Am using Excel 2010.
    Is it possible to change the color of the tab for the active sheet?
    I am thinking like red instead of white.


  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Manchester, United Kingdom
    Thanked 15 Times in 14 Posts
    Hi Albert

    That's an interesting question so here may be an answer using event handler macros. If you don't now how to write a macro, do this:

    Save your workbook as an XLSM so you can have macros.
    Go to developer tab and open the VBA editor.
    Double click the ThisWorkbook object in the left pane and write this code in the right pane:

    Private oldindex As Integer

    Sub workbook_sheetactivate(ByVal Sh As Object)
    oldindex = Sh.Tab.ColorIndex
    Sh.Tab.ColorIndex = 3
    End Sub

    Sub workbook_sheetdeactivate(ByVal Sh As Object)
    If oldindex <> 0 Then Sh.Tab.ColorIndex = oldindex
    End Sub

    That SHOULD make the active tab underline in red without changing the colour of any other tabs. I can't say I've tested it very much. Unfortunately it is a characteristic of Excel 2010 that the active tab is only underlined, not coloured all over. If you prefer another colour try different colorindex values other than 3. There's a list here:


  4. The Following User Says Thank You to iansavell For This Useful Post:

    abaas (2011-12-08)

  5. #3
    Silver Lounger
    Join Date
    Jan 2001
    West Long Branch, New Jersey, USA
    Thanked 0 Times in 0 Posts

    This seemed a useful function, so I decided to add it to an Excel 2003 workbook to see how it performed - the code seemed simple enough that I thought it might work there too.

    The problem I ran into is that once the code is added, I can't change the color of a tab (even from no color). When I activated a sheet (after the code was added and compiled), I changed the tab/sheet's color. But once I clicked on another tab, the deactivated sheet's color reverted back to its color from before the change.

    Looking at the code, I can see why the above is so. Not sure if Excel 2003 behaves differently in this regard than 2010 (I have 2010 but didn't test it there since I don't use it much).

    What one would want is someway to know when a sheet tab color changes and update the oldindex but I don't think there's a way to do this (the SheetChange event doesn't seem applicable).

    Does the code work for 2010 even if a tab color is changed after the code has been added?



  6. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 79 Times in 75 Posts
    Something like this I guess:
    Option Explicit
    Private oldindex         As Integer
    Const lTAB_COLOUR        As Long = 3
    Sub workbook_sheetactivate(ByVal Sh As Object)
       oldindex = Sh.Tab.ColorIndex
       Sh.Tab.ColorIndex = lTAB_COLOUR
    End Sub
    Sub workbook_sheetdeactivate(ByVal Sh As Object)
       If oldindex <> 0 Then
          If Sh.Tab.ColorIndex = lTAB_COLOUR Then Sh.Tab.ColorIndex = oldindex
       End If
    End Sub
    as long as you don't want to make the tab colour the same as the selected highlight colour.
    Microsoft MVP - Excel.

Posting Permissions

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