Time Stamp based on latest edit of a cell

[OversizedCranium]

Hi All,

I'm struggling using other examples to convert a VBA code to make a cell timestamp when something on a checklist is updated.

For example, this goes down for around 20 rows. I have a cell S5, which is the first in a list of what I want to be time stamps for when someone updates the status of their task in J5, I want this repeated for all tasks which go to row 33,

How would I go about doing this? So far I have it date when it's been changed, but it changes the whole column!

Thanks,

 

[Logit]

Paste this formula in S5 and drag it down to S33.

=IF(J5<>"",NOW(),"")

 

[OversizedCranium]

Paste this formula in S5 and drag it down to S33.

=IF(J5<>"",NOW(),"")

Hi,

I had this in funnily enough, but whenever any of the cells in column J they all get updated, rather than just the one in the same row

 

[Logit]

If the formula is correctly dragged down the column, the J5 in the formula will auto change for each row.

Paste the formula in S5. Left click and hold on the small black box in the lower right corner, drag down the column to S33 and release.

 

[OversizedCranium]

If the formula is correctly dragged down the column, the J5 in the formula will auto change for each row.

Paste the formula in S5. Left click and hold on the small black box in the lower right corner, drag down the column to S33 and release.

Yes I don't have any absolute cell references on, it still relates to the corresponding row in terms of formula...

I.E, it's J5 - J6 - J7 - J8 etc. However when one single cell is change the whole date stamp updates for all

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("J5:J33")) Is Nothing Then
      Target.Offset(, 9).Value = Now
   End If
End Sub

This needs to go in the relevant sheet module.

 

[OversizedCranium]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("J5:J33")) Is Nothing Then
      Target.Offset(, 9).Value = Now
   End If
End Sub

This needs to go in the relevant sheet module.

Fluff pulling it out the bag since 2014.

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[OversizedCranium]

Glad we could help & thanks for the feedback.

If I could pick your brains when you're here, Is there a possibility this could also be used to signify the changer?

I.E if I changed it, we could have the date & in a corresponding Cell it said 'OversizedCranium'

I get that it's overkill for my use here, I'm more curious than anything

 

[Fluff]

You could use

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("J5:J33")) Is Nothing Then
      Target.Offset(, 9).Value = Now
      Target.Offset(, 10).Value = Environ("username")
   End If
End Sub