Results 1 to 4 of 4

Thread: Change Sheet Tab color based on date in cell of different Sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change Sheet Tab color based on date in cell of different Sheet

    I am trying to find a code that will work to change sheet tab color to red if a cell in a different sheet has a date that is less than today's date in it.

    Ex. Column H in Sheet1 has registration dates in it, Sheets2-30 have vehicle information in them.
    Sheet2 tab turns red if the date in H1 of Sheet1 is less than today's date.
    Sheet3 tab turns red if the date in H2 of Sheet1 is less than today's date.
    Etc.


    Can anyone please help me? Even if a code can't affect them all at once, I would really like to be able to individually code each Sheet to change red when that vehicles registration date is less than today's date on the first Sheet.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,488
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Change Sheet Tab color based on date in cell of different Sheet

    Welcome to the forum!
    Here's some sheet code you can install that will automatically change tab colors when ever a change is made to the date in H1:H30 of the sheet.
    Assumes that your sheets are organized in ascending order with Sheet1 being the first sheet.

    To install sheet code:
    1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
    2. Copy the code below from your browser window and paste it into the white space in the VBE window.
    3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
    4. Make sure you have enabled macros whenever you open the file or the code will not run.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Intersect(Target, Range("H1:H30")) Is Nothing Then
        For Each c In Intersect(Target, Range("H1:H30"))
            If IsEmpty(c) Then Sheets(c.Row + 1).Tab.Color = xlNone
            If IsDate(c.Value) Then
                If c.Value < Date Then
                    Sheets(c.Row + 1).Tab.Color = vbRed
                End If
            End If
        Next c
    End If
    End Sub
    Once you have installed the code, you can get things going by entering a date in H1:H30 that's earlier than today. Deleting that date should remove the color.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,488
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Change Sheet Tab color based on date in cell of different Sheet

    Apologies, I found an error in the code I posted earlier. Please replace ti with the code below.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Intersect(Target, Range("H1:H30")) Is Nothing Then
        For Each c In Range("H1:H30")
            If IsEmpty(c) Then Sheets(Application.Min(c.Row + 1, 30)).Tab.Color = xlNone
            If IsDate(c.Value) Then
                If c.Value < Date Then
                    Sheets(Application.Min(c.Row + 1, 30)).Tab.Color = vbRed
                End If
            End If
        Next c
    End If
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,819
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Change Sheet Tab color based on date in cell of different Sheet

    Try this:
    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the Master sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    The script will run when the sheet is activated.


    Code:
    Private Sub Worksheet_Activate()
    'Modified  9/20/2019  4:27:34 PM  EDT
    Dim i As Long
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "H").End(xlUp).Row
    For i = 1 To LastRow
        If Cells(i, 8).Value < Date Then
            Sheets(i).Tab.Color = vbRed
        Else
            Sheets(i).Tab.Color = vbWhite
        End If
    Next
    End Sub
    Last edited by My Aswer Is This; Sep 20th, 2019 at 04:34 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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