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

[Mythramaer]

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.

 

[JoeMo]

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.

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]

Apologies, I found an error in the code I posted earlier. Please replace ti with the code below.

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]

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.

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