Code that updates date automatically in adjacent cell, Now how to apply it to multiple Cells.

[scout2489]

Good Afternoon,

I looked around and pieced together this code that when a formula result changes in C3 it will put the current date in D3 to record the change. Now I am trying to figure out how to do this for C4 to D4, D5 to D5 and so on until C27 and D27. Could someone please help me with this?

Option Explicit
Public C3ValueOld As Variant


Private Sub Worksheet_Calculate()
    If Range("C3").Value <> C3ValueOld Then
        Range("D3").Value = Now()
        C3ValueOld = Range("C3").Value
    End If
End Sub

Thank you for any help or suggestions.

 

[NeonRedSharpie]

Option Explicit
Public C3ValueOld As Variant


Private Sub Worksheet_Calculate()


    For x = 3 To 27
    If Range("C" & x).Value <> C3ValueOld Then
        Range("D" & x).Value = Now()
        C3ValueOld = Range("C" & x).Value
    End If
     Next x
End Sub

I bet there's a non-looping way to do it, but for such a small data set it shouldn't impact run time. My only concern is the logic of matching it all to C3ValueOld and how that gets passed to the function.

 

[scout2489]

Thank you for the quick reply. I am getting a Compile Error: Variable not defined. Any ideas why?Thanks

 

[NeonRedSharpie]

You shouldn't have to define x, but sometimes people come back saying you have to. So you could try to add:

Dim x as Long

 

[scout2489]

Alright I fixed that.Ok so now I need to change the variable X to be just the cell that has changed. Right now it will fill the whole range with today's date. I understand why it is doing this but I cannot think of the right way to change it to reflect. For X = 3 to 27 that has changed.

 

[scout2489]

Good Morning,To make this more clear I am trying to find a way to get adjacent cells to change when the formula calculates the new result from a new sheet. So C3 calculates and D3 updates with the current date and time. The formula I currently have which is above fills all cells in D3 to D27 when any update is made to C3 to C27. Thank you for your help!