Auto enter data time

trebor1956 · Sep 5th 2017

I have a worksheet where in cell A5 has an item description. Then in week 1 a quantity will be entered into Cell B5 and I would like a date time auto entered into cell C5. In week 2 a quantity will be entered into Cell D5 and I would like a date time auto entered into cell E5. This would continue for numerous weeks of readings.
I have seen how a VBA code could work for one cell but not for multiple entries that I would like.

I hope someone can help, it would save me an awful lot of work.

Many thanks,

skywriter · Sep 6th 2017

Re: Auto enter data time

Try this:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub


If Not Intersect(Range("B5:B10"), Target) Is Nothing Then
    Target.Offset(, 1).Value = Now
End If


End Sub

NoSparks · Sep 6th 2017

Re: Auto enter data time

The way I understand the situation, maybe this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    'limit to single cell
        If Target.Count > 1 Then Exit Sub
    'limit to row 5
        If Target.Row <> 5 Then Exit Sub
    'limit to even numbered columns
        If Target.Column Mod 2 = 1 Then Exit Sub
        
    'prevent this procedure from calling itself
        Application.EnableEvents = False
    'put date and time in cell on right
        Target.Offset(0, 1).Value = Now
    're-enable events
        Application.EnableEvents = True
End Sub

Display More

Auto enter data time

Participate now!

Share

Users Viewing This Thread