Copying cell information based on date

Good Morning,

I'm trying to get certain cells to populate information from another cell based on a date. I.E. - I want O4:Z4 to copy what is in cell D8 (an amount) when O2:Z2 are Date(Now) or greater. I need to show when something in D8 is actually 0 and also if/and when the next date occurs and D8 changes that it doesn't change the information in the previous O4:Z4 cell. My code is listed below and the document is attached for a better understanding of it all.

Can anyone help?!?!? PLEASE!

Private Sub Worksheet_Calculate()


On Error Resume Next
If DateValue(Range("F4:F5")) = "" Then Exit Sub

On Error GoTo WorkSheet_Calculate_ErrorHandler

If Range("O4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("O2")) Then
      Range("O4").Value = Range("C8").Value
    End If

End If

If Range("P4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("P2")) Then
      Range("P4").Value = Range("C8").Value
    End If

End If

If Range("R4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("R2")) Then
      Range("R4").Value = Range("C8").Value
    End If

End If

If Range("S4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("S2")) Then
      Range("S4").Value = Range("C8").Value
    End If

End If

If Range("T4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("T2")) Then
      Range("T4").Value = Range("C8").Value
    End If

End If

If Range("U4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("U2")) Then
      Range("U4").Value = Range("C8").Value
    End If

End If

If Range("V4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("V2")) Then
      Range("V4").Value = Range("C8").Value
    End If

End If

If Range("W4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("W2")) Then
      Range("W4").Value = Range("C8").Value
    End If

End If

If Range("X4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("X2")) Then
      Range("X4").Value = Range("C8").Value
    End If

End If

If Range("Y4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("Y2")) Then
      Range("Y4").Value = Range("C8").Value
    End If

End If

If Range("Z4").Value = "0" Then

    If DateValue(Now) >= DateValue(Range("Z2")) Then
      Range("Z4").Value = Range("C8").Value
    End If

End If



Exit Sub

WorkSheet_Calculate_ErrorHandler:
Stop


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If DateValue(Now) = DateValue(Range("O2").Value) Then

    If Range("O4").Value <> Range("C8").Value Then
      Range("O4").Value = Range("C8").Value
    End If
   
End If

If DateValue(Now) = DateValue(Range("P2").Value) Then

    If Range("P4").Value <> Range("C8").Value Then
      Range("P4").Value = Range("C8").Value
    End If

End If

If DateValue(Now) = DateValue(Range("R2").Value) Then

    If Range("R4").Value <> Range("C8").Value Then
      Range("R4").Value = Range("C8").Value
    End If

End If

If DateValue(Now) = DateValue(Range("S2").Value) Then

    If Range("S4").Value <> Range("C8").Value Then
      Range("S4").Value = Range("C8").Value
    End If

End If

If DateValue(Now) = DateValue(Range("T2").Value) Then

    If Range("T4").Value <> Range("C8").Value Then
      Range("T4").Value = Range("C8").Value
    End If

End If

If DateValue(Now) = DateValue(Range("U2").Value) Then

    If Range("U4").Value <> Range("C8").Value Then
      Range("U4").Value = Range("C8").Value
    End If
End If

If DateValue(Now) = DateValue(Range("V2").Value) Then

    If Range("V4").Value <> Range("C8").Value Then
      Range("V4").Value = Range("C8").Value
    End If
End If

If DateValue(Now) = DateValue(Range("W2").Value) Then

    If Range("W4").Value <> Range("C8").Value Then
      Range("W4").Value = Range("C8").Value
    End If
End If

If DateValue(Now) = DateValue(Range("X2").Value) Then

    If Range("X4").Value <> Range("C8").Value Then
      Range("X4").Value = Range("C8").Value
    End If
End If

If DateValue(Now) = DateValue(Range("Y2").Value) Then

    If Range("Y4").Value <> Range("C8").Value Then
      Range("Y4").Value = Range("C8").Value
    End If
End If

If DateValue(Now) = DateValue(Range("Z2").Value) Then

    If Range("Z4").Value <> Range("C8").Value Then
      Range("Z4").Value = Range("C8").Value
    End If
End If

If Not Intersect(Target, Range("F6")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
With Target.Offset(-2, 21)
    .Formula = Range("C8").Value
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If



If Not Intersect(Target, Range("F7")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
With Target.Offset(-3, 22)
    .Formula = Range("C8").Value
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
    



End Sub

Only problem with that is the $0 amount in O4 has now been updated to $1,000. How do I keep that at $0?

Essentially yes. D8 will change throughtout time based on additional information being entered in. In the example that I attached, I did not enter in $1,000 in F14 which updates D8 until 10/22/14. So I wanted 10/21/14 (O2) to be 0 and 10/22/14 (P2) to be $1,000. However, since R2:V2 have already happened I wanted them to also populate what is already in D8 which would be $1,000 and if something was entered in additional amount in F15 which would then take the total of F14 + F15 and calculate that in D8,today's date of 10/27/14 (V2) would be the "new" amount in D8.

In the example I gave, 10/21 had already passed and information was entered in on 10/22 which populated that cell. Now I just need it to populate all the cells in between 10/22 and today with $1000 because nothing else has been entered.

I tried adding your formula to a blank version of the example and it keeps looping and freezes up. Any suggestions?

Could my problem be having a formula (=Max(G14:G522)) in C8 and trying to run a macro to read C8 depending on certain days?