display a date

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi
when i enter a number into a cell i want the cell next to it to display the
date the number was entered
 
Hi,

Right click your sheet tab, view code and paste the code below in. It
currently works on data entered in column A so change to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy")
Application.EnableEvents = True
End If
End Sub

Mike
 
Hi Richard

If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security) before trying out Mike's code.

If this post helps click Yes
 
Hi,

Since you are only interested to get the date when you enter a number try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Or Not IsNumeric(Target)
Then Exit Sub
If Not Intersect(Target, [A:A]) Is Nothing Then
Target.Offset(, 1) = Date
End If
End Sub

If you are only doing this for a single column, here column A:A, then you
don't need to disable events... However if you want this to work for the
entire worksheet then you need to include them.
 
that works but the date changes every day i need it to stay the date the
number was entered

thanks
 
that works but the date changes every day, i need it to stay the date the
number was entered
 
how do i include events?
i have about 20 columns that i want this to work with. i need the date to
come up in the cell to the left of the cell that i put the number in

thanks

Shane Devenshire said:
Hi,

Since you are only interested to get the date when you enter a number try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Or Not IsNumeric(Target)
Then Exit Sub
If Not Intersect(Target, [A:A]) Is Nothing Then
Target.Offset(, 1) = Date
End If
End Sub

If you are only doing this for a single column, here column A:A, then you
don't need to disable events... However if you want this to work for the
entire worksheet then you need to include them.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Richard said:
Hi
when i enter a number into a cell i want the cell next to it to display the
date the number was entered
Click to expand...
Click to expand...
 
Hi Richard

Try the below...modified to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
IF Target.Offset(, 1) = "" Then
Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy")
End If
Application.EnableEvents = True
End If
End Sub


If this post helps click Yes
 
Back
Top