Insert Timestamp Automatically While Entering Data In Different Column

[Gillian2022]

When a price is placed in column h3 - n3 I need the date to be stamped in the column r3 - x3. I don't want the price to change as the date changes. I want to stay the date that i inputted. Once I have the formula can I copy it down and across? This is my formula now.

 

[bebo021999]

Right click on sheet name, view code, then paste below code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H3:N3")) Is Nothing Then Exit Sub
Target.Offset(0, 10).Value = Date
End Sub

 

[Gillian2022]

Hello,

This is new to me. After I copy and paste then what do I do? How do i get it to work in my sheet? Thanks

 

[Joe4]

Hello,

This is new to me. After I copy and paste then what do I do? How do i get it to work in my sheet? Thanks

You don't need to do anything special.
As long as the values in H3-N3 are being manually entered in (and not the result of formulas), it should work automatically as you update those cells.

 

[Gillian2022]

Hello, I got it to work but if i remove data from h3 the date still stays in r3. Also, how do i copy to other cells?

 

[Joe4]

If you want the date in column R removed if the price in column H is removed, you can update the original code to this:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

If Intersect(Target, Range("H3:N3")) Is Nothing Then Exit Sub

If Target.Value = "" Then
    Target.Offset(0, 10).ClearContents
Else
    Target.Offset(0, 10).Value = Date
End if

End Sub

Please explain what you mean by this, maybe walk us through an example of what you are wanting to do.

Also, how do i copy to other cells?

 

[Gillian2022]

Hello,

My objective is to input a prices in column h3 - n3 and each row after. So h4 - n4 down to at least row h50 - n50. I want the date to update only if i change data in any cell between h and n.

 

[Joe4]

Hello,

My objective is to input a prices in column h3 - n3 and each row after. So h4 - n4 down to at least row h50 - n50. I want the date to update only if i change data in any cell between h and n.

I think you just need to change this reference:
Range("H3:N3")
to this:
Range("H3:N50")

 

[Gillian2022]

Ok, changing to the new range works but if i delete data in the cell the date remains.

 

[Joe4]

Ok, changing to the new range works but if i delete data in the cell the date remains.

How exactly are you deleting the data?
Are you actually deleting it, or overwriting it with a single space?
The code is looking for an empty cell after deletion, so using a single space would not work (unless we were to amend the code to do that).

If you still cannot get it to work, please walk us through an actual example telling us:
- What cell you are updating (range address)
- How exactly you are deleting the data
- What cell should have the date deleted (range address)