Update excel date if any cells in a range change

[alan1203]

Hi I was hoping somebody can provide me with an easy was of updating a date in an excel cell if I change any cells in a range in a worksheet.
For example;
If I change any of the data in range "A2:G2" then the date in "H2" will update to todays date.

Thanks Alan

 

[Gavona]

thread68-1569946 i can't really take credit as John gave you this in the office forum!

Gavin

 

[alan1203]

Sorry for the repeat post but my interpretation of Johns second post in my office thread was that my problam would best be solved using VBA and that I should post another thread in this forum. Could you be kind enough to offer anymore advice on this? Thanks, Alan

 

[PHV]

In the worksheet's procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("A2:G2")) Is Nothing) Then
  Application.EnableEvents = False
  Range("H2") = Date
  Application.EnableEvents = True
End If
End Sub[/ode]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alan1203]

Thank you PHV.
If I was wanting to do the same for other rows can I just copy this and edit the ranges? or is there another way of doing this?
Thanks

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alan1203]

The code you gave me works fine with the one line that I asked for and i was thinking about just copying that code for each line in the list but the list I have could grow in the future and I was hoping to avoid having to go back into the VBA editor each time as i've no clue what i'm doing.
I tried to amend the code you gave me to;

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("A:G")) Is Nothing) Then
Application.EnableEvents = False
Range("H:H") = Date
Application.EnableEvents = True
End If
End Sub

and it triggers whenever I change any of the rows, but it changes all the values in column H when I just want it to change Column H of the row that i've edited. Hope i'm on the right track and thanks again for your time.

 

[PHV]

Replace this:
Range("H:H") = Date
with this:
Cells(Target.Row, "H") = Date

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886