Inserting current date into column "A" when value met

[ABabeNChrist]

I have a piece of code that will insert the current date into column "A" when data is entered into column "B" of same row. The code works as designed.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
        Application.EnableEvents = False
        With Target(1, 0)
            .Value = Now
        End With
    End If
    Application.EnableEvents = True
End Sub

But now I’ve have been trying to figure out a way so that if there is already data / date in column "A" the code would then exit sub and not update with current date
I’ve tried different things with no success

[mbarron]

Try this:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, Range("B2:B1000")) Is Nothing _
        And Target.Offset(, -1) = "" Then
        Application.EnableEvents = False
        With Target(1, 0)
            .Value = Now
        End With
    End If
    Application.EnableEvents = True
End Sub

[HansV]

I'd use a slightly different version that works even if the user edits a range of cells, for example by pressing Ctrl+Enter:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
        Application.EnableEvents = False
        For Each oCell In Intersect(Target, Range("B2:B1000"))
            If oCell.Value <> "" And oCell.Offset(0, -1).Value = "" Then
                oCell.Offset(0, -1).Value = Now
            End If
        Next oCell
        Application.EnableEvents = True  
    End If
End Sub

If you only want to enter the date instead of the date and time, change Now to Date in the line that sets the value of the cell in column A:

oCell.Offset(0, -1).Value = Date

[ABabeNChrist]

Thank you mbarron and Hans
As always most greatly appreciated