Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb Change sheet tab color in Excel 2010 based on conditionally formatted cells

    Hello there!

    I am new to WSLounge and fairly new to some of the more advanced functions of Excel 2010 but I'm working on a project and trying to learn my way through changing the worksheet tab color of my spreadsheet based on several individual cells that are conditionally formatted on only that specific worksheet.

    For example, TODAY() is in cell D16. I have a conditionally formatted rule that if that date is less than or equal to 3 days from EOMONTH and the dropdown menu I've created in D17 has "INCOMPLETE" selected then D16 will turn Red. I'm looking for a way to make the worksheet tab turn red if D16 is red based on those conditions. D16 is also formatted to turn yellow for a range of 4-10 days from EOMONTH and purple if 11-15 days from EOMONTH. I have several cells on several sheets within my workbook formatted this way and want to color the corresponding sheet tabs to alert the user if there are red cells within that sheet.

    Is this possible? I'm having a hard time finding information on this specific task and am a complete novice when it comes to writing code....

    Thank you SO much in advance if you can help me figure this out.

  2. 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
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    This will trigger based on changing D17. If you need to change under other circumstances you will have to let us know. Be aware this type of macro will disable UNDO in the entire spreadsheet.

    Steve
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D17")) Is Nothing Then
      If Range("D17") = "INCOMPLETE" And _
        DateSerial(Year(Date), Month(Date) + 1, 0) - Range("D16") <= 3 Then
        Me.Tab.Color = 255
      Else
        Me.Tab.ColorIndex = xlNone
      End If
    End If
    End Sub

    PS, if you have lots of cells that you want to test, you may want to use a formula to look for any "conditions me" and then use a calculate macro to loop through the sheets and change the colors of those meeting the conditions to red and those that do not to non-colored instead of creating code to check for each and every occurence...
    Last edited by sdckapr; 2014-06-03 at 09:18.

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    You could use something like this:
    Code:
    Private Sub Worksheet_Calculate()
        Me.Tab.ColorIndex = Range("D16").DisplayFormat.Interior.ColorIndex
    End Sub
    which I don't think will clear the Undo stack.
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. The Following User Says Thank You to rory For This Useful Post:

    sdckapr (2014-06-04)

Tags for this Thread

Posting Permissions

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