Enter date in Column A based on another row

[debauch]

Hi,

Currently I have the below code that enters a date in the corresponding cell in Column A. It updates no matter what cell I change ... it is possible to restrict this to change in just column G?

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
Range("A" & Target.Row).Value = Date 
End Sub

EDIT: Is is possible to format the date the include hh:mm also? (i.e 10/11/2008 18:40pm)

BONUS: An easy way to adjust it to Eastern TZ no matter where file is utilized??

[stanleydgrom]

debauch,

If I understand you correctly, for changes to column G ONLY, you want column A, same row, to reflect the current date and time.

If the above statement is correct, then here you go.

 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Range("A" & Target.Row)
        .Value = Now
        .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
        .Value = .Value
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

We could also add code, that if you delete an entry in column G, the corresponding cell (row) in column A will also be deleted.


Have a great day,
Stan

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
If Not Intersect(Target, Me.Columns(1)) Is Nothing Then
    Me.Range("A" & Target.Row).Value = Now
End If
End Sub

[debauch]

Sa-weeet! Thank-you. The second response I will have to play with - it seems a little more dynamic or easier to update if I require for multiple columns - thank you both. First one worked like a charm !

Is it tough to normalize the data to EST - or should I attempt this on the database side?

[GTO]

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
If Not Intersect(Target, Me.Columns(1)) Is Nothing Then
Me.Range("A" & Target.Row).Value = Now
End If
End Sub

Minor tweak:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Me.Columns(7)) Is Nothing Then
Me.Range("A" & Target.Row).Value = Format(Now(), "Mm/Dd/yy Hh:Nn ""Hours""")
End If
End Sub

I believe you were looking to date/time-stamp Column 1 ("A") if the equivalent row in Column 7 ("G") changed. Mostly wanted to point out the correct column, as this appeared to "throw you".

Did include stanleydgrom's suggestion as to formatting, just handled a bit differently. (As I've not heard of referring to MIL time with an 'AM/PM' designator, changed that.)

Have a great weekend!

Mark