snowbounduk
10-21-2010, 05:53 AM
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
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