Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Auto Tab Color (Excel 2003)

    How can I change a tab color, based on a cell color in that worksheet?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Tab Color (Excel 2003)

    Changing the color of a cell doesn't trigger an event in Excel, so I don't see an easy way to make Excel automatically update the color of the sheet tab when the user changes the color of a cell.
    You could use the Worksheet_Change event to update the color of the sheet tab when the user changes the value of a cell, e.g.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
    Select Case Range("A1")
    Case "Red"
    Me.Tab.Color = vbRed
    Case "Green"
    Me.Tab.Color = vbGreen
    Case "Blue"
    Me.Tab.Color = vbBlue
    Case "Yellow"
    Me.Tab.Color = vbYellow
    Case Else
    Me.Tab.ColorIndex = xlColorIndexNone
    End Select
    End If
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Thanks, Hans. I will give that a try. I will be looking at a cell that is conditionally formatted based on its content; e.g., if B37 text=WIP, color cell yellow, and I would like the tab color to reflect the latest color in B37. 'ppreciate the fast response, as always! (When do you sleep?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Tab Color (Excel 2003)

    You could put the same conditions in the code that you use for the conditional formatting of the cell.

    > When do you sleep?

    At night. It's 3:15 PM where I live, so I'm wide awake... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Since I'm using Excel's Format > Conditional Formatting > Condition 1 thru 3 for B2 and B37, I'm not sure how to also color the tab as well as the cell color in the Condition panes. (Or even if it's possible?)

    Q1) How could I Conditionally Format B2 with 4 conditions, as I'm able to do with the tab color; Format > Conditional Formatting > seems to stop at 3 conditions.

    My current code is below, sorta kludged together, since I'm lacking in much VBA skills.

    Q2) How would I rewrite it so the B37 condition rules, even when B2 changes? As written, the last change to either B2 or B37 colors the tab.

    Q3) I tried writing this code in a VBA Module, but couldn't get it to work. So I applied it to each desired sheet, via right-click Tab > View Code. What am I missing?

    Q4) My workbook has 15 sheets, but I only want this code to apply to sheets 3-14. So I copied the code to each of the Tab > View Code panels. There must be a better way?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
    Select Case Range("B2")
    Case "Done"
    Me.Tab.ColorIndex = 50
    Case "WIP"
    Me.Tab.ColorIndex = 6
    Case "Help"
    Me.Tab.ColorIndex = 3
    Case "TBD"
    Me.Tab.ColorIndex = 39
    Case Else
    Me.Tab.ColorIndex = xlColorIndexNone
    End Select
    End If
    If Not Intersect(Range("B37"), Target) Is Nothing Then
    Select Case Range("B37")
    Case "Complete"
    Me.Tab.ColorIndex = 33
    Case Else
    Me.Tab.ColorIndex = xlColorIndexNone
    End Select
    End If
    End Sub

    I have a couple of somewhat related questions that I'll post separately. Thanks for sharing your wisdom!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Tab Color (Excel 2003)

    Q1) Conditional formatting has a limit of 3 conditions in Excel 2003 and before. If you need more, you must use the Worksheet_Change event procedure or upgrade to Excel 2007.

    Q2) If you want B37 to determine the sheet tab color, you should remove the code that checks B2, obviously.

    Q3) Worksheet_Change is a worksheet event procedure. It *must* be in the worksheet module, otherwise it won't do anything.

    Q4) You could use the Workbook_SheetChange event instead. This is a workbook event; the code for it *must* be in the ThisWorkbook module, otherwise it won't do anything.
    Workbook_SheetChange passes a sheet argument Sh as well as a range argument Target. Sh is the sheet on which one or more cells were changed. You must take care to refer to Sh throughout the code.
    <code>
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Index
    Case 3 To 14
    If Not Intersect(Sh.Range("B37"), Target) Is Nothing Then
    Select Case Sh.Range("B37")
    Case "Complete"
    Sh.Tab.ColorIndex = 33
    Case Else
    Sh.Tab.ColorIndex = xlColorIndexNone
    End Select
    End If
    Case Else
    ' Do nothing
    End Select
    End Sub</code>

  7. #7
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Thanks for your replies, Hans. Here are some notes.
    Q1) That's what I thought; thanks for the confirmation.
    Q2) Obviously, I didn't explain myself well. I need the tab color to change if the color of B2 OR B37 changes, with the B37 change overriding any B2 change. That's why I put the B37 code after the B2 code. If B37 does not equal "Complete", the tab color should reflect the B2 color. So I can't just remove the code that checks B2.
    Q3) I get it; thanks.
    Q4) Good info; I will try the _SheetChange event. If the sheet range is not contiguous, I assume I would express it in the Case statement as Case 3 to 12, 15, 22 [for example]?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Tab Color (Excel 2003)

    Q2/Q4: try the following code (adapting the sheet indexes as necessary):
    <code>
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Index
    Case 2, 5 To 8, 11 To 13, 15
    If Not Intersect(Sh.Range("B2,B37"), Target) Is Nothing Then
    Select Case Sh.Range("B37")
    Case "Complete"
    Sh.Tab.ColorIndex = 33
    Case Else
    Select Case Sh.Range("B2")
    Case "Done"
    Sh.Tab.ColorIndex = 50
    Case "WIP"
    Sh.Tab.ColorIndex = 6
    Case "Help"
    Sh.Tab.ColorIndex = 3
    Case "TBD"
    Sh.Tab.ColorIndex = 39
    Case Else
    Sh.Tab.ColorIndex = xlColorIndexNone
    End Select
    End Select
    End If
    End Select
    End Sub
    </code>
    As you see, I have nested the Select Case ... End Select block for B2 within the Case Else part of the Select Case ... End Select block for B37, because you only want to look at B2 if B37 does not contain "Complete".

  9. #9
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Excellante! That looks great. I will give it a try.

  10. #10
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Hans,

    At first I did not know how to attach the code: "the code for it *must* be in the ThisWorkbook module." After flailing around in VB Help, I found the way, maybe worth explaining for any newbe's who find this thread: Open the VB Editor, in the VBAProject window, find the desired workbook if several are open, find and right click on ThisWorkbook, View Code. That's where it goes. It worked perfectly.

    Q5) If there is conflicting code attached to a sheet, which rules, the sheet code or the ThisWorkbook code? I may try this to see if I can answer it myself.

    Thanks for the very good (and rapid) help.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Tab Color (Excel 2003)

    Other ways to open the ThisWorkbook module:
    - If you're already in the Visual Basic Editor: double-click ThisWorkbook in the Project Explorer on the left hand side.
    - From Excel: Right-click the Excel workbook icon on the left hand side of the menu bar (if the workbook is maximized) or in the title bar of the workbook (if the workbook is not maximized), and select View Code from the popup menu.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Tab Color (Excel 2003)

    <hr>Q5) If there is conflicting code attached to a sheet, which rules, the sheet code or the ThisWorkbook code? I may try this to see if I can answer it myself.<hr>

    Conflicting in what way?

    If you have code in both, the worksheet events are run first and then the workbook events. See Chip Pearson's primer on Events In Excel VBA

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Hans - thanks for the Other Ways to Open tips.

  14. #14
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Tab Color (Excel 2003)

    Steve - "conflicting" as in the sheet code colors the sheet tab one way and the workbook tab colors the sheet tab some other way. Thanks for your explanation and the link!

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Tab Color (Excel 2003)

    Then you need to remove the "conflict" from the code. It would seems to be a "logic" issue in what you are telling VBA to tell excel to do...

    Steve

Posting Permissions

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