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

Mythramaer

New Member
Joined
Sep 20, 2019
Messages
1
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,556
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,462
Messages
5,636,419
Members
416,917
Latest member
koto1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top