VBA Automatically copy Cell value to another Cell based on the results of a formula

[kopower]

Hi I have been struggling trying to create this code for the last two weeks and am hoping someone can help me .
I want to copy the value (Date) and pastespecial as a result of a formula.
For instance I have formula in B2 which is IF(B3="","",IF(B3>=80%,TODAY(),"")) as soon as the result of the formula shows today's date I want it to automatically copy the value into B4, in C2 formula IF(c3="","",IF(c3>=80%,TODAY(),"")) as soon as the result shows today's date I want it to automatically copy the value into c4, in d2 formula IF(d3="","",IF(d3>=80%,TODAY(),"")) as soon as the result shows today's date I want it to automatically copy the value into d4 , etc for e, f, and g.

Hoping that someone will be able to help me on this as I really been struggling trying to get this to work.

Thank you in advance for anybody's help on this.

Regard.

KO

 

[JLGWhiz]

I don't know if you can use a Worksheet_Change event code to do that if the values in B2 and B3 are both derived by calculation. Excel is geared to ignore calculations for the Change event.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    If Range("B3").Value = Date Then Range("B4") = Date
    If Range("C3").Value = Date Then Range("C4") = Date
Application.EnableEvents = True
End Sub

I'm not sure I understand which value you wanted copied, but you can work with the code to get the right range. Just notice you wanted this for the entire data range. Will post that later.

 

[JLGWhiz]

This covers columns B:G

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    For Each c In Range("B3:G3")
        If c.Value = Date Then c.Offset(1) = Date
    Next
Application.EnableEvents = True
End Sub

In case it was the row 2 value you wanted copied then use this one.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    For Each c In Range("B3:G3")
        If c.Value = Date Then c.Offset(1) = c.Offset(-1).Value
    Next
Application.EnableEvents = True
End Sub

 

[kopower]

Hi JLGWhiz that works great is it also possible to have something that if c.Value does not equal Date that it is blank? Therefore it would switchback and forth depending on the result of B3:G3

 

[kopower]

Hi JLGWhiz I just figured out the second question that I had sent you. So please ignore previous post.

Thank you so much for your answer I spend over 40 hours trying to figure this out. You are fantastic and thank you again for all your help!!!!!!

 

[JLGWhiz]

Hi JLGWhiz I just figured out the second question that I had sent you. So please ignore previous post.

Thank you so much for your answer I spend over 40 hours trying to figure this out. You are fantastic and thank you again for all your help!!!!!!

You're welcome,
regards, JLG