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

    Excel 2010 Condition Tab coloring

    Hello,

    I am trying to make individual tab sheets conditionally linked to be the same color as an individual conditioned cell color. I am trying to do this across an entire work book. The goal is that I can link these tabs to due dates and can quickly look at the work book and see which "tab" needs my attention. is this possible ?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes, you can.

    Instead of using the "conditioned cell colour", you will find it far easier (to write and to debug) the VBA you need if you set the tab colour using the same test that the conditional formatting was using.

    Then the VBA line you need to set the tab colour of the worksheet "SheetName" will be along the lines of ActiveWorkbook.Sheets("SheetName").Tab.ColorIndex = [number]

    You will have to decide how you want to trigger this code running, manually or in response to some event.

    You can read about the colour codes in Excel 2010 here: http://www.exceldigest.com/myblog/20...2010-vba-code/
    Last edited by MartinM; 2014-10-18 at 06:20.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Silencer,

    As Martin said it doesn't seem to work with activecell.interior.color color codes. See code commented out in the routine below. The solution is to match the colors in the conditional format to the color codes used in vba on the worksheet change event.

    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect     As Range
       Dim lTabColor As Long
       
       
       Set isect = Application.Intersect(Range("A1"), Target)
       If isect Is Nothing Then
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
         
    '     lTabColor = Target.Interior.Color
    '     Debug.Print lTabColor
    '     With ActiveWorkbook.ActiveSheet.Tab
    '         .Color = lTabColor
    '         .TintAndShade = 0
    '     End With
    
         Select Case WorksheetFunction.Round(Now() - Target, 0)
               Case Is > 90
                   ActiveSheet.Tab.Color = RGB(255, 0, 0)
               Case Is > 60
                   ActiveSheet.Tab.Color = RGB(255, 192, 0)
               Case Is > 30
                   ActiveSheet.Tab.Color = RGB(255, 255, 0)
               Case Else
                   ActiveSheet.Tab.Color = RGB(0, 255, 0)
         End Select
    
         Application.EnableEvents = True '*** Reset Events ***
       End If
    
    End Sub
    condfmttabs.JPG

    Test file: VBA - Excel - Change Tab Color to Cell Color.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Silencer,

    This code will check cell A1 against the current date when the workbook is opened. If within 1 week due, the sheet tab will turn red else the sheet tab will have no color. Change [a1] in code line 9 to which ever cell has your due date and change the 7 in code line 10 to however as many days prior you want the tab to change color.

    HTH,
    Maud

    Code:
    Private Sub Workbook_Open()
    '-----------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim sht As Worksheet
        Dim count As Integer
    '-----------------------------------------------------
    'CYCLE THROUGH EACH SHEET AND CHECK DATE
        For Each sht In ActiveWorkbook.Sheets
            count = sht.[a1] - Date
            If count <= 7 And count >= 0 Then 'WITHIN 1 WEEK DUE
                sht.Tab.Color = 255
            Else: 'NOT WITHIN 1 WEEK DUE
                sht.Tab.Color = xlAutomatic
            End If
        Next sht
    End Sub
    Last edited by Maudibe; 2014-10-17 at 19:50.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Silencer,

    Following the same concept of changing the sheet tab color when a cell change was made to a specific cell:

    If you wish to test the conditional formatting of the cell to determine if True or False then use this code in the worksheet module. If you change the date and your condition is True then the sheet tab will turn Red. If False then sheet tab will have no color. This code evaluates the first conditional rule for the cell. If the third cell rule is the rule you are monitoring for True, change the 1 to a 3 in line 2.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.FormatConditions(1)
        test = Evaluate(.Formula1)
    End With
    If test = True Then
        ActiveSheet.Tab.Color = vbRed
    Else:
        ActiveSheet.Tab.Color = xlAutomatic
    End If
    End Sub
    Last edited by Maudibe; 2014-10-19 at 12:42.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In 2010 and later, it's actually pretty simple since you can use the DisplayFormat property added in 2010 to get the colour of a cell even if it's the result of CF.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Maudibe (2014-10-20)

Posting Permissions

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