Time stamp using VBA

[data808]

I have 2 time stamps. One is for start and the other is for finished. These will trigger when the user types into a specific range of cells. Right now it's working fine but I want to make another change to these ranges that will make it flow better for our use.

Right now, if the user types into column A8 through A88 or C8 through C88 (column B is locked), it will auto-fill column D's cell (which is the start time) within that same row with the current time in military format (HH:MM). This next range is much bigger. If the user types into columns F:O (so basically all the other columns in between F and O) starting from row 8 through 88 then it will auto-fill column E's cell (which is the finish time) within that same row with current time in military format (HH:MM). Here is the code to give you an idea of what I am talking about:

'start time stamp
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("A:C"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("D" & Target.Row)
            If Not IsDate(.Value) And .Value = "" Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With

' finish time stamp
    With Target
        If .Count > 1 Then Exit Sub
            If Not Intersect(Range("F:O"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("E" & Target.Row)
            If Not IsDate(.Value) And .Value = "" Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With

I would like to get rid of the finish time stamp VBA and apply it to the existing start time stamp VBA. What I mean by this is when the user makes an entry it will put in the finish time stamp in the row above of it into column E but also put a start time stamp for that same row they are typing for column D. The reason is that when they are starting a new entry, they are also finishing up their previous entry so that is why I would like it to do it at the same time. The data that is entered into the F:O range would be entered at the same time they start. All this time I been holding off typing into F:O range because that is what would trigger the finsih time stamp and I didn't like having to hold off entering that data just to trigger the finish time stamp. I would rather it trigger at the same time I am entering the next entry. Does that make sense?

Any help is appreciated. Thank you in advance.

 

[data808]

I have 2 time stamps. One is for start and the other is for finished. These will trigger when the user types into a specific range of cells. Right now it's working fine but I want to make another change to these ranges that will make it flow better for our use.

Right now, if the user types into column A8 through A88 or C8 through C88 (column B is locked), it will auto-fill column D's cell (which is the start time) within that same row with the current time in military format (HH:MM). This next range is much bigger. If the user types into columns F:O (so basically all the other columns in between F and O) starting from row 8 through 88 then it will auto-fill column E's cell (which is the finish time) within that same row with current time in military format (HH:MM). Here is the code to give you an idea of what I am talking about:

'start time stamp
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("A:C"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("D" & Target.Row)
            If Not IsDate(.Value) And .Value = "" Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With

' finish time stamp
    With Target
        If .Count > 1 Then Exit Sub
            If Not Intersect(Range("F:O"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("E" & Target.Row)
            If Not IsDate(.Value) And .Value = "" Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With

I would like to get rid of the finish time stamp VBA and apply it to the existing start time stamp VBA. What I mean by this is when the user makes an entry it will put in the finish time stamp in the row above of it into column E but also put a start time stamp for that same row they are typing for column D. The reason is that when they are starting a new entry, they are also finishing up their previous entry so that is why I would like it to do it at the same time. The data that is entered into the F:O range would be entered at the same time they start. All this time I been holding off typing into F:O range because that is what would trigger the finsih time stamp and I didn't like having to hold off entering that data just to trigger the finish time stamp. I would rather it trigger at the same time I am entering the next entry. Does that make sense?

Any help is appreciated. Thank you in advance.

It wouldn't allow me to edit my original post on the last paragraph. Made some changes to hopefully make it sound a bit more clear. Sorry if it's confusing:

I would like to get rid of the finish time stamp VBA and apply it to the existing start time stamp VBA. What I mean by this is when the user makes an entry it will put in the finish time stamp in the row above of it into column E but also put a start time stamp for that same row they are typing for column D. The reason is that when they are starting a new entry, they are also finishing up their previous entry so that is why I would like it to do it at the same time. All this time I been holding off typing into the F:O range because that is what would trigger the finish time stamp and I don't like having to hold off entering that data just to trigger the finish time stamp when I actually finish. I would rather it trigger at the same time I am entering the next entry. Does that make sense?

 

[bebo021999]

Try:

'start time stamp
If Intersect(Target, Range("A:C")) Is Nothing Or Target.Count>1 Then Exit Sub
With Range("D" & Target.Row)
    .NumberFormat = "HH:MM"
    .Value = Now
End With

'end time stamp
With Range("E" & Target.Row - 1)
    .NumberFormat = "HH:MM"
    .Value = Now
End With

Does it work?

 

[data808]

Try:

'start time stamp
If Intersect(Target, Range("A:C")) Is Nothing Or Target.Count>1 Then Exit Sub
With Range("D" & Target.Row)
    .NumberFormat = "HH:MM"
    .Value = Now
End With

'end time stamp
With Range("E" & Target.Row - 1)
    .NumberFormat = "HH:MM"
    .Value = Now
End With

Does it work?

Thanks for the reply. This works pretty well except for a couple things. It keeps updating the time if I need to change one of the values in cells A or C at a later time. Is there a way to not trigger the time stamp if D and E already has a time entered in it? Also how would I handle the first entry in row 8? E7 is locked and since its the first entry, there is no finish time that is necessary for the user.

 

[bebo021999]

'start time stamp
If Intersect(Target, Range("A:C")) Is Nothing Or Target.Count>1 Then Exit Sub
With Range("D" & Target.Row)
    If Not IsDate(.Value) And .Value = "" Then
        .NumberFormat = "HH:MM"
        .Value = Now
    end if
End With

'end time stamp
With Range("E" & Target.Row - 1)
        If Not IsDate(.Value) And .Value = "" And Target.Row <> 8 Then
             .NumberFormat = "HH:MM"
            .Value = Now
        End If
End With

 

[data808]

'start time stamp
If Intersect(Target, Range("A:C")) Is Nothing Or Target.Count>1 Then Exit Sub
With Range("D" & Target.Row)
    If Not IsDate(.Value) And .Value = "" Then
        .NumberFormat = "HH:MM"
        .Value = Now
    end if
End With

'end time stamp
With Range("E" & Target.Row - 1)
        If Not IsDate(.Value) And .Value = "" And Target.Row <> 8 Then
             .NumberFormat = "HH:MM"
            .Value = Now
        End If
End With

Works perfectly! Thank you very much.