Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

Good morning! Yes, there is a way to automatically track the last modified date of a cell, column, or row in Excel. You can use the built-in function "NOW()" to capture the current date and time, and then use a simple VBA code to update the "Last modified date" column whenever a cell in the corresponding row is changed.

Here are the steps to set this up:

1. Open your Excel worksheet and select the column where you want to track the last modified date.
2. Right-click on the column header and choose "Insert" to add a new column next to it. This will be your "Last modified date" column.
3. In the first cell of the "Last modified date" column (B2 in your example), enter the formula "=IF(A2<"",NOW(),"")". This will display the current date and time whenever the corresponding cell in column A is not empty.
4. Press "Enter" to apply the formula to the cell.
5. Select the cell with the formula (B2) and copy it.
6. Select the entire "Last modified date" column (B2:B7 in your example) and paste the formula by pressing "Ctrl+V".
7. Now, whenever you change a cell in column A, the corresponding cell in column B will update with the current date and time.

To make this process automatic, you can use a VBA code that will run whenever a cell in column A is changed. Here's how:

1. Press "Alt+F11" to open the Visual Basic Editor.
2. In the Project Explorer window, double-click on the worksheet where you want to track the changes.
3. In the code window that opens, paste the following code:

4. Save the workbook and close the Visual Basic Editor.

Now, whenever you change a cell in column A, the corresponding cell in column B will update with the current date and time automatically, without you having to type anything.

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

Thank You. Very Helpful.

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

Gary,
The macro works fine but there is any other way to set a date without
running a macro?

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

If you want a static date, you can select the cell and hit ctrl-; (control
semicolon).

If you want a static time, you can hit ctrl-:



Mary wrote:

Gary,
The macro works fine but there is any other way to set a date without
running a macro?

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007


--

Dave Peterson

Hello,

This works for me, but it has a side effect of not allowing any un-dos.
After changing the tracked cell, CTRL-Z does not do anything. Is there a way
to preserve the undo history?

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007