auto enter date when another cell populated?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an s/sheet with order data: I need to create a macro or formula so
that when data is entered into i.e. Column C, the date is automatically
entered into Column A and the time into Column B. The dates / times need to
remain static / constant and not update when the file is reopened. Any
suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and
Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help!
 
You need a macro, try this. right click on the sheet where you want this to
happen and paste this code in.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C1:C65536"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
With .Offset(0, -1)
.NumberFormat = "hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Was that OK?

Mike
 
No... I must have missed something. Pasting the code only displays the
code... no activity in the cells.

Is there not a function that would recognize when a cell is not blank and
date/time stamp another cell? (Sorry - still a newbie and trying to
understand... and not familiar with VisualBasic). - Thanks!
 
Zim,

Sorry I read my instructions again and I've misled you. Right click on the
sheet tab and then click view code and paste the code in there.

With the code entered there when you type in column C. The date & time will
then be entered in columns A&B respectively.

Mike
 
right click sheet tab>view code>insert this.>test

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Application.EnableEvents = False
Target.Offset(, -2) = Date
Target.Offset(, -1) = Time
Application.EnableEvents = True

End Sub
 
Back
Top