Insert Date based on range updating

[scotthannaford1973]

Hi all

I have a sheet with data in B2:O2 that dynamically links to another sheet (B2:O2 basically summarises data from elsewhere).

I would like, in A2, to put the latest date of when any of the cells in range B2:O2 change. The only code I have found puts the date into offset columns - which is no good as that would depend on which cell changes... I want the date to be added only to A2 when any of B2:O2 change.

thanks in advance!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a change in B2:O2 and press the RETURN key or TAB key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:O2")) Is Nothing Then Exit Sub
    Range("A2") = Date
End Sub

 

[scotthannaford1973]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a change in B2:O2 and press the RETURN key or TAB key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:O2")) Is Nothing Then Exit Sub
    Range("A2") = Date
End Sub

thanks, Mumps - that's spot on

 

[mumps]

You are very welcome.