date stamp when cell changed

[richardjshaffer]

Hi,

hope someone can help, very simply I want to capture the date and time that a cell is changed.

So if the value in cell A2 is changed, then cell B2 date stamps with the current time; similiarly if cell A3 is changed, then cell B3 date stamps...

Tried looking at previous threads, I'm a bit confused by one that seems to work with a personal sub? Please can someone help, I'm no expert in macros but if there's a simple one I can use, or a formula based solution, I'd be very grateful,
thanks, Richard

 

[hiker95]

ATSJ,

Or, do you want the Worksheet_Change event macro code to check range A2:A1000, and, range C2:E1000, and, write the date and time stamp to the target.row in column B?

 

[ATSJ]

ATSJ,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


If you want the Worksheet_Change event macro code to check range A2:E1000 for changes, then, what column, to the right of column E, do you want the date and time written to?

Thanks for the reply. I'm on Win 10, PC.

Column F to the right of the range of cells would be perfect, writing the updated date/time to the relevant row that had a cell change.

 

[ATSJ]

ATSJ,

Or, do you want the Worksheet_Change event macro code to check range A2:A1000, and, range C2:E1000, and, write the date and time stamp to the target.row in column B?

Actually, checking A2:A1000, B2:B1000 and C2:C1000 writing the date to column D would be a situation I could envisage being useful. I'm particularly interested that the date is changed for the row in question though, so if an entry in B9 is changed the date is updated in D9, or an entry in C21 is changed the date is written to D21. Especially useful would be to have the background the particular cell change colour so that the edit is clearly identifiable, but that might be stretching things.

 

[hiker95]

ATSJ,

Here is an updated Worksheet_Change event for you to consider, based on your latest request.

Excel 2007
	A	B	C	D
1
2	test1			8/19/2015 2:39:19 PM
3		test2		8/19/2015 2:39:26 PM
4			test3	8/19/2015 2:39:32 PM
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/19/2015, ME396311
If Intersect(Target, Range("A2:C1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Target.Interior.Color = vbYellow
With Range("D" & Target.Row)
  .Formula = Now
  .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Before you use the WorkSheet_Change event code with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then make changes to range A2:C1000

 

[ATSJ]

ATSJ,

Here is an updated Worksheet_Change event for you to consider, based on your latest request.

Excel 2007

	A	B	C	D
1
2	test1			8/19/2015 2:39:19 PM
3		test2		8/19/2015 2:39:26 PM
4			test3	8/19/2015 2:39:32 PM
5

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/19/2015, ME396311
If Intersect(Target, Range("A2:C1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Target.Interior.Color = vbYellow
With Range("D" & Target.Row)
  .Formula = Now
  .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Before you use the WorkSheet_Change event code with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then make changes to range A2:C1000

I appreciate the help very much. Is there a way of limiting the time that the updated cell is highlighted for?

 

[hiker95]

I appreciate the help very much. Is there a way of limiting the time that the updated cell is highlighted for?

ATSJ,

I could adjust the code, so that if the next cell in range A2:C1000 is changed, that the prior cell(s) in range A2:C1000 will have there color removed.

 

[ATSJ]

ATSJ,

I could adjust the code, so that if the next cell in range A2:C1000 is changed, that the prior cell(s) in range A2:C1000 will have there color removed.

Wow, that would be brilliant.

 

[hiker95]

ATSJ,

Thanks for the Private Message, and, kudos.

Here is another Worksheet_Change event macro for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/19/2015, ME396311, Version3
If Intersect(Target, Range("A2:C1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("A2:C1000").Select
With Selection.Interior
  .Pattern = xlNone
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With
Target.Select
Target.Interior.Color = vbYellow
With Range("D" & Target.Row)
  .Formula = Now
  .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Remember, you can only have ONE Worksheet_Change event macro in your worksheet.

Then make changes to range A2:C1000.

 

[ATSJ]

ATSJ,

Thanks for the Private Message, and, kudos.

Here is another Worksheet_Change event macro for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/19/2015, ME396311, Version3
If Intersect(Target, Range("A2:C1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("A2:C1000").Select
With Selection.Interior
  .Pattern = xlNone
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With
Target.Select
Target.Interior.Color = vbYellow
With Range("D" & Target.Row)
  .Formula = Now
  .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Remember, you can only have ONE Worksheet_Change event macro in your worksheet.

Then make changes to range A2:C1000.

Thanks, it's appreciated.

 

[hiker95]

ATSJ,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.