Date-Time stamp question

BenSmile

New Member
Joined
Jun 23, 2019
Messages
2
Hi,

I'm fairly new to Excel and trying to setup 2 Columns. Column A will have a regular text entry (name) and then I'd like column B to give a Date and Time stamp every time I enter a new text in each respective cell in the A column. I tried various methods I found online but nothing worked.

Could someone help me out?

Thanks,
Ben
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Welcome to the forum!

this is sheet event code for the sheet you are trying to set up.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Range("A:A"))
        If c.Value <> "" Then
            c.Offset(0, 1).Value = Now
        Else
            c.Offset(0, 1).Value = ""
        End If
    Next c
    Range("B:B").EntireColumn.AutoFit
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/24/2019  12:35:06 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 1 Then
Target.Offset(, 1).Value = Now
End If
End Sub
 
Last edited:
Upvote 0
Thank you so much Joe for the explanation and sharing of the code. Works perfectly. I changed it a bit to shift the effect to different cells See Below:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Range("A:A"))
        If c.Value <> "" Then
            c.Offset(0, 5).Value = Now
        Else
            c.Offset(0, 5).Value = ""
        End If
    Next c
    Range("F:F").EntireColumn.AutoFit
    Application.EnableEvents = True
End If
End Sub


Another question. Maybe a little more difficult?
In column “D” can there be a drop down menu with the option “Out’ and “In” ? And have the option for each entire row to be marked as green when the option “In” has been selected? And have “Out” selected by default?

If that's possible it would be incredible!

Ben
 
Upvote 0
Thank you so much Joe for the explanation and sharing of the code. Works perfectly. I changed it a bit to shift the effect to different cells See Below:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Range("A:A"))
        If c.Value <> "" Then
            c.Offset(0, 5).Value = Now
        Else
            c.Offset(0, 5).Value = ""
        End If
    Next c
    Range("F:F").EntireColumn.AutoFit
    Application.EnableEvents = True
End If
End Sub


Another question. Maybe a little more difficult?
In column “D” can there be a drop down menu with the option “Out’ and “In” ? And have the option for each entire row to be marked as green when the option “In” has been selected? And have “Out” selected by default
?

If that's possible it would be incredible!

Ben
You are welcome - thanks for the reply. For your question, take a look at conditional formatting in Excel Help.
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top