Reputation: 35
I have managed to write above mentioned code but it is not what actually I required. My goal is partly fulfilled but not fully.
I am trying to create a datestamp when the status which is a drop down menu in the first column is switched to other, it returns a date.
The problem I am facing is highligted and mark as bold. For that particular status the code is running perfect, returning start date and end date but it stops executing for the rest.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
On Error GoTo Fìn
Application.EnableEvents = True
Dim rng As Range
For Each rng In Intersect(Target, Columns(1))
If LCase(rng.Value) = "clearing" Then
Cells(rng.Row, 2) = Date
Cells(rng.Row, 2).NumberFormat = "dd.mm.yyyy"
Else
If LCase(rng.Value) <> "clearing" Then
Cells(rng.Row, 4) = Date
Cells(rng.Row, 4).NumberFormat = "dd.mm.yyyy"
Else
If LCase(rng.Value) = "wait for start" Then
Cells(rng.Row, 5) = Date
Cells(rng.Row, 5).NumberFormat = "dd.mm.yyyy"
ElseIf rng.Offset(0, 13).Value = 3 Then
Cells(rng.Row, 15) = Date
Cells(rng.Row, 15).NumberFormat = "dd.mm.yyyy"
Else
If LCase(rng.Value) = "ongoing" Then
Cells(rng.Row, 8) = Date
Cells(rng.Row, 8).NumberFormat = "dd.mm.yyyy"
Else
If LCase(rng.Value) = "in umsetzung" Then
Cells(rng.Row, 10) = Date
Cells(rng.Row, 10).NumberFormat = "dd.mm.yyyy"
Else
If LCase(rng.Value) = "prämiert" Then
Cells(rng.Row, 12) = Date
Cells(rng.Row, 12).NumberFormat = "dd.mm.yyyy"
End If
End If
End If
End If
End If
End If
Next rng
End If
Fìn:
Application.EnableEvents = True
End Sub
Upvotes: 0
Views: 83
Reputation: 1118
Actually Sam deserves the credit being the first to point it out in comment.
If LCase(.Value) = "clearing" Then
Cells(rng.Row, 2) = Date
Cells(rng.Row, 2).NumberFormat = "dd.mm.yyyy"
Else
If LCase(rng.Value) <> "clearing" Then
Cells(rng.Row, 4) = Date
Cells(rng.Row, 4).NumberFormat = "dd.mm.yyyy"
Else 'this prevents everything else from executing, the text either equals "clearing" or does not equal it
Upvotes: 1