Tab Color Dependent on Date

[default_name]

Hello!

I am new here, but I have a question regarding tab colors.

I have a workbook that is split up into pay periods for a record of timekeeping.
Each pay period is represented on an individual tab (there are several tabs in the workbook [the workbook contains pay periods for the entire year]).

I am trying to write a macro that would automatically highlight the worksheet tab (or change the tab color to something like yellow) that corresponds to the current pay period.

Dates for each day in the pay period are displayed in the following cells of each worksheet:

D1:H1
D19:H19

​

Basically, I want the tab to highlight based on the current date so it is easier to see which tab/pay period I should be on.


Not sure if my question is making sense...

ie: If today's date is 5/16/18 (which it is), then the worksheet in the workbook where 5/16/18 is found in one of those cells (D1:H1 or D19:H19) should have a yellow tab.
​

I am not so sure I am writing the macro correctly.
Here is what I have so far:

Private Sub Worksheet_Calculate()

    If Range("D1:H1","D19:H19").Value = "Today()" Then
        Me.Tab.ColorIndex = 6   ' Yellow
    Else
        Me.Tab.ColorIndex = -4142       ' No Color
    End If
    
End Sub

Thanks in advance!

 

[default_name]

Hey guys!

When I opened my spreadsheet this morning I noticed that the tab is no longer colored in.
For the past month or so it has worked perfectly.

I haven't changed or even touched anything in the VBA code.

How come, all of the sudden, it isn't working?
Some of the sheets in the workbook are protected (to prevent any user edit changes), but that is the only recent change I can think of.

Thanks in advance

------------
EDIT/UPDATE:
Never mind. I fixed it.
A new column was inserted in the sheet yesterday. This threw off all of the VBA code.
I updated the VBA code for the correct range. This fixed it.

 

[default_name]

Today's date is 1/7/2019.
My tab titled 0107-0118 is highlighted correctly (since today is 0107 [Jan 7]).

I looked at my workbook today and noticed an error.
For some reason the tab titled 1028-1108 (which is for Oct 28 to Nov 8; obviously not this week) is being highlighted. No other tabs are being affected. Just this one.

Here's what I have for the workbook code:

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      Set Fnd = Ws.Range("F1:J1,F21:J21").Find(Date, , , , , , , , False)
      Ws.Tab.Color = IIf(Fnd Is Nothing, False, vbBlack)
   Next Ws
End Sub

In worksheet 1028-1108 cells F1:J1 are
10/28/2019
10/29/2019
10/30/2019
10/31/2019
11/1/2019

cells F21:J21 are
11/4/2019
11/5/2019
11/6/2019
11/7/2019
11/8/2019

None of these dates should result in a highlighted tab.

What am I missing/doing wrong here?

 

[Fluff]

Try

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      Set Fnd = Ws.Range("F1:J1,F21:J21").Find(Date, , , [COLOR=#ff0000]xlWhole[/COLOR], , , , , False)
      Ws.Tab.Color = IIf(Fnd Is Nothing, False, vbBlack)
   Next Ws
End Sub

 

[default_name]

You are amazing! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback