Enter timestamp when value is selected from drop down

Question

In column A I have data validation that lets a user select a value from a list.

When the value Complete is selected, I want the cell to the right in column B to show the date it was changed to Complete

The data validation runs from A1:A2500 so would want the rule to apply to B1:B2500

My attempt at doing this would only work for Cell A1 & B1,

How can i modify this to work for the required range ?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1")) Is Nothing Then
       Select Case Range("A1").Value
           Case "Complete"
               Range("B1").Value = Now
           Case Else
               Range("B1").Value = 0
        End Select
    End If

End Sub

score 0 · Answer 1 · answered Dec 04 '19 at 11:40

SOLVED

For anyone else having a similar issue the below is working fine for me:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("A1:A2500"), Target)
xOffsetColumn = 1
If WorkRng = "Complete" Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Enter timestamp when value is selected from drop down

1 Answers1

Linked