Date-Time stamp question

[BenSmile]

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

 

[JoeMo]

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.

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

 

[My Aswer Is This]

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

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

 

[BenSmile]

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:

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

 

[JoeMo]

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:

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.