display a date

[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

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[Shane Devenshire]

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.

 

[Richard]

that works but the date changes every day i need it to stay the date the
number was entered

thanks

 

[Richard]

that works but the date changes every day, i need it to stay the date the
number was entered

 

[Richard]

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

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

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

 

[Jacob Skaria]

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