Mani
Mani

Reputation: 35

Macro time stamp

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

Answers (1)

user3819867
user3819867

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

Related Questions