You can use the following macro in the worksheet module for Sheet1 (right-click on sheet tab and select View Code, then paste this code into the vb editor window). Anytime your sheet calculates it should reset column C to 0 if column H changes to TRUE in that row.
Private Sub Worksheet_Calculate()
Dim lastrow As Long, i As Long
With Sheets("Sheet1")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Application.EnableEvents = False
For i = 2 To lastrow
If .Range("H" & i).Value = True Then .Range("C" & i).Value = 0
Next i
Application.EnableEvents = True
End With
End Sub
I also changed two of your formulas a little.
In cell G2 use this instead of the nested IF's. Fill down col G as far as needed:
Formula:
=LOOKUP(F2,{0,1,2,3,4,5,6,7,8,9},{0,24,36,48,50,52,56,64,72,80})
In H2 you don't need the IF statement. The AND function will either result in TRUE or FALSE automatically. (Plus, your T/F results were strings, not boolean values.) Fill down col H as far as needed.
Formula:
=AND(MONTH(TODAY())=MONTH(E2),DAY(TODAY())=DAY(E2))
Bookmarks