Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2014
    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 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.

    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
        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.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts
    You could use something like this:
    Private Sub Worksheet_Calculate()
        Me.Tab.ColorIndex = Range("D16").DisplayFormat.Interior.ColorIndex
    End Sub
    which I don't think will clear the Undo stack.

    Microsoft MVP - Excel

  4. 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