Date weekly reset notify using VBA

[FoRmEd]

I use a code that is supposed to reset every saturday (@ 00:01) because that is the week ending day.

The issue, I need the week ending to stay Saturday but reset on Sunday (@ 00:01) instead. I think Sunday is 0, but I dont know for sure.

Here is the code I am currently using:

Sub Worksheet_Activate()
    Dim YesNo As Variant
 
    If Range("D2").Value < Now Then
        YesNo = MsgBox("Update Week Ending date?", vbYesNo + vbQuestion, "Update")
            If YesNo = vbYes Then
                ActiveSheet.Unprotect Password:="IRSROCKS"
                dif = "7" - Format(Date, "w")
                lstDOW = Format(Now + dif, "d mmm yyyy")
                ActiveSheet.Range("D2") = CDate(lstDOW)
                ActiveSheet.Protect Password:="IRSROCKS"
            Else
                MsgBox "Please make sure to update the Week Ending date"
            End If
    End If
End Sub

 

[FoRmEd]

Any ideas?