Reset button after anniversary date (vacation schedule)

11/15 solved!

sharing some functions... also used a case statement to do this with a button.

ActiveSheet.Unprotect
' temp1 = Range("p6").Value
' Range("p6").Value = ""

Range("p5").Value = Range("p6").Value
' Range("p5").Value = temp1
Range("B14:AF37").Select
Selection.ClearContents
Range("u7").Value = 40

Range("U8").Select
Selection.ClearContents
'------------------------------

Select Case Range("P7").Value
Case 0
'Range("p5").Value = Range("p6").Value

Range("P4").Value = 40
Case 1

Range("P4").Value = 80
Case 2

Range("P4").Value = 80
Case 3
Range("P4").Value = 120
Case 4
Range("P4").Value = 120
Case 5
Range("P4").Value = 120
Case 6
Range("P4").Value = 120
Case 7
Range("P4").Value = 120
Case 8
Range("P4").Value = 120
Case 9
Range("P4").Value = 120
Case 10
Range("P4").Value = 160
Case Else
Range("P4").Value = 160
End Select




** update 11/14**
added my basic menu (click the blue shape)
-its just a place holder. the values come from the database, not the vb code.
stuck on how to use them to set the actual cells in the subsheets

** update*
I really just need to learn how to use the "define name" fields as row/column lookups then paste the hours into that cell.

example: select name (which is the column), select date (which is the row)...
then have a function to place the "hours selected" into the cell... that is for this name and date selection.

i'm confused on how to do that... the user selects type of vacation(which is the sheet name).. so the formula basically:
needs to find the sheet, look up the date/name... at this cell insert the hours.

Just a start or hint would be nice... my formulas havn't worked yet.

IF THERE IS AN ALTERNATE METHOD PLEASE SHARE

goals:
1. after anniversary date passes, row for user turns red or any color
2. a reset button so that once you know their anniversary date has passed it resets the static time off hours, sets the new "carry over hours" and adjusts the vacation hours for the new year (may also need to reset the sub tables which i can figure out later)

I did post in the functions forum but my goals have changed and I honestly do not think this is possible with a simple function.

Currently I am using a lookup table.
Old goal: reset carry over hours after 3 months which they would expire (deemed unnecessary, i can skip this step)

for goal #1:
I think I could compare the date/month with anniversary date's date month correct? (but i think it would need to be reset the day after their anniversary date to prevent a reloop/constant reset of values)

for goal #2: reset button: most likely needs to be in a specific order

a. carry over hours = previous year's vacation hours remaining
b. vacation hours per year gets re-adjusted:
=VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)
c. personal hours per year is static: 40
(therefore the secondary field which is a function will self adjust for remaining personal hours)
d. sick hours per year = 40 hours, will need to be reset similiar to "step c"
e. Fl hol = 8 (floating holiday gets reset)
f. other = not sure, this might still be manually adjusted and can be ignored for now

other notes:
1year=40 hours
after 1 year anniversary =80 hours
3 years-9 =120 hours
10 years+ = 160hours
Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)