Time Stamp based on latest edit of a cell

OversizedCranium

New Member
Joined
Aug 18, 2015
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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,
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,856
Paste this formula in S5 and drag it down to S33.

VBA Code:
=IF(J5<>"",NOW(),"")
 

OversizedCranium

New Member
Joined
Aug 18, 2015
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Paste this formula in S5 and drag it down to S33.

VBA Code:
=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

Well-known Member
Joined
Aug 31, 2016
Messages
3,856
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

New Member
Joined
Aug 18, 2015
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,036
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Aug 18, 2015
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,036
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

OversizedCranium

New Member
Joined
Aug 18, 2015
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,036
Office Version
  1. 365
Platform
  1. Windows
You could use
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top