How to update cell on change

[Guest]

I have two columns: A and B. Column A is a text summary. Column B is a date
field. Each time I make any change to a cell in column A, I would like the
corresponding cell in column B to update with today's date, thus showing when
the most recent change was made. Any help would be appreciated.

 

[Dave Peterson]

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Gord Dibben]

J.P.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub


This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks Dave!
This was a very helpful link. I was able to update on change exactly as I
wanted to with the macro provided.

JP

 

[Guest]

Gord,
this also worked. The code is a little different from Dave's response, but
the same outcome! Thanks for your help. JP

 

[CLR]

Private Sub Worksheet_Change(ByVal Target As Range)
'Macro inserts date in Column B for change in Column A
ActiveCell.Select
If ActiveCell.Column = 1 Then 'Limits macro action to column A
If Not Selection Is Nothing Then
Application.Selection.Offset(0, 1).Value = Date
End If
Else
End If
End Sub

Vaya con Dios,
Chuck, CABGx3