PDA

View Full Version : [SOLVED] Enter date in Column A based on another row



debauch
10-11-2008, 02:39 PM
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
10-11-2008, 03:03 PM
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
10-11-2008, 03:05 PM
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
10-11-2008, 03:22 PM
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
10-12-2008, 04:11 AM
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