Conditional Format multiple worksheet tabs color based on a date in each sheet

[FadeToOrange]

Hi Everyone,

I'm building a workbook to track and monitor the recruitment plans for several job positions. The workbook has 2 informational sheets 8 sheets for the roles needing to be filled (1 sheet for each of the 8 jobs). In cell A10 of each job sheet is the recruitment start date. I'm trying to create a way to change the color of the job tab to green when that date is within 1 week or less of the date in cell A10. I've poked around on other posts that were similar and attempted the VBA scripts suggested - tweaking to my specs - and have the below script which works if I go into each tab and update the date, but I'd like it to update all the tabs all the time based on the current date. The file usually sits open on my computer and I'm not always updating the individual sheets, but want to be able to see in real time which sheets are "active" (in the recruitment process period) or not.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Range("A10").Value - 7 >= Now() Then
Sh.Tab.ColorIndex = -4142 ' No Color
Else
Sh.Tab.ColorIndex = 10 ' Green
End If
End Sub



Appreciate the help and input.
Mike

 

[kevin9999]

What's the names of the 2 "informational sheets"? I think this would be best handled with a workbook_open event, but we'd need to know the names of those two sheets to exclude them from the code execution.

 

[FadeToOrange]

What's the names of the 2 "informational sheets"? I think this would be best handled with a workbook_open event, but we'd need to know the names of those two sheets to exclude them from the code execution.

"People Plan" and "Resources"

 

[kevin9999]

Try the following Workbook Open event code on a copy of your file:

Option Explicit
Private Sub Workbook_Open()
    Dim ws As Worksheet, start
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "People Plan", "Resources"
            Case Else
                start = ws.Range("A10")
                If IsDate(start) Then
                    If Date >= (start - 7) Then
                        ws.Tab.ColorIndex = 10
                    Else
                        ws.Tab.ColorIndex = xlColorIndexNone
                    End If
                Else
                    ws.Tab.ColorIndex = xlColorIndexNone
                End If
        End Select
    Next ws
End Sub

If you're not familiar with adding Workbook event code, this video may be useful:

 

[FadeToOrange]

That worked great! Thank you so much.

 

[kevin9999]

That worked great! Thank you so much.

You're welcome, and thanks for the feedback

 

[FadeToOrange]

Ok so one more question, is it possible to also trigger when something is changed on "DASHBOARD"? So right now it will only trigger and change color of tab to green if I close and reopen the file, but I also need it to change if I make changes to the sheets. It is set up so you input the recruitment plan start dates for each of the jobs on the Dashboard sheet and then it updates the corresponding job sheet with the info. So if I could have it trigger if there is any change on "Dashboard" sheet and then it runs the event again on each ws checking the date to see if its in the parameter to change the tab color to green. Hope that makes sense.

 

[kevin9999]

So I'm guessing that "DASHBOARD" is another sheet not previously mentioned? I guess you want it excluded from the initial workbook open event code as well? If I'm guessing correctly, then add the following code to the worksheet event code area for the sheet "DASHBOARD":

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim ws As Worksheet, start
        For Each ws In ThisWorkbook.Worksheets
            Select Case ws.Name
                Case "People Plan", "Resources", "DASHBOARD"
                Case Else
                    start = ws.Range("A10")
                    If IsDate(start) Then
                        If Date >= (start - 7) Then
                            ws.Tab.ColorIndex = 10
                        Else
                            ws.Tab.ColorIndex = xlColorIndexNone
                        End If
                    Else
                        ws.Tab.ColorIndex = xlColorIndexNone
                    End If
            End Select
        Next ws
    Application.EnableEvents = True
End Sub

And add "DASHBOARD" to the relevant workbook open event code line, like this:

Case "People Plan", "Resources", "DASHBOARD"