PDA

View Full Version : How can we update the date in a cell when we modify a text cell?



stt01029
04-13-2011, 09:47 AM
Hi everyone,

One quick question that I cannot find a solution is;

I have a text cell (with comments) and I want to connect it with a date cell, that updates the date (to the current day) everytime I am making changes to the text cell (with the comments)

Cell 1 Cell 2any comment17-Apr

When I change cell 1 - I want to get the current date (when I modified it) in cell 2.

Is there any way to do that with excel??

Thank you all in advance!

Have a nice day!

BrianMH
04-13-2011, 11:09 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then


Range("A2").Value = Now
End If

End Sub


Put this in your worksheet module and change addresses as necessary

stt01029
04-13-2011, 11:22 AM
That's really great - thank you very much for the quick response!

Is there any possibility that we could add sth in the code, that would repeat the same action (a loop?) for many different cells?

i.e as in the excel file attached

Thanks once again!

BrianMH
04-13-2011, 11:46 AM
Not exactly sure what you need but:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Range("B" & Target.Row) = Now
End If

End Sub

Will make it so that any time you change a cell in column A, column B will update to the current date/time.

If you want it to update column B any time a cell is updated no matter the column (other than B of course) then it would be:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then
Range("B" & Target.Row) = Now
End If

End Sub

stt01029
04-13-2011, 12:53 PM
That's brilliant - Many thanks once again!