Solved: Date stamp required when cell changes from "open" to "closed"

[snowbounduk]

I have a spreadsheet which contains a log. THe final column in the current log has a drop down menu with open or closed as the options.

In the adjacent cells I would like to date stamp the first column when "open" is selected and in the second column a date stamp when "closed" is selected. This needs to be doen for every row in the log.

I have tried the following code but I get entries in random places. I don't know how to fix this.

Any help is much appreciated!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AH:AH")) Is Nothing Then
Application.EnableEvents = False
If (Cells(7, 34) = "Open") Then
Cells(7, 35) = Date
Cells(7, 36) = ""
End If
If (Cells(7, 34) = "Closed") Then
Cells(7, 35) = ""
Cells(7, 36) = Date
End If
Application.EnableEvents = True
End If
End Sub

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("AH:AH")) Is Nothing Then
Application.EnableEvents = False
With Target
If Me.Cells(7, .Column) = "Open" Then
Me.Cells(7, .Column + 1) = Date
Me.Cells(7, .Column + 2) = ""
End If
If Me.Cells(7, .Column) = "Closed" Then
Me.Cells(7, .Column + 1) = ""
Me.Cells(7, .Column + 2) = Date
End If
End With
Application.EnableEvents = True
End If
End Sub

[snowbounduk]

Many thanks for that.

1 problem, no matter which cell in the sheet I change, the date is put in cell AI7. Also, the date is put in the same place wether open or closed is selected.

Suggestions welcome!

Cheers.

[Kenneth Hobs]

If I understand what you want, change the 7's to Target.Row.

[snowbounduk]

Thank you Kenneth, that works a treat!

Now my only problem is that when I change from open to closed, I lose the opened date.

Is there a way to keep the opened date when I change the line to closed? I am trying to then run some stats on the duration between opening and closing the issue.

Many thanks.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("AH:AH")) Is Nothing Then
Application.EnableEvents = False
With Target
If Me.Cells(7, .Column) = "Open" Then
Me.Cells(7, .Column + 1) = Date
Me.Cells(7, .Column + 2) = ""
End If
If Me.Cells(7, .Column) = "Closed" Then
Me.Cells(7, .Column + 2) = Date
End If
End With
Application.EnableEvents = True
End If
End Sub

[snowbounduk]

Thanks for that. I should have been able to work that out!

Thanks again.