D
diablo
Hello,
Looking for some more help with spreadsheet code. This spreadsheet that I'm
working on is functioning correctly with the exception of cell "(0,7)". When
the time value in cell "(0,5)" is displayed I want to print "1st Shift" or
"2nd Shift" or "3rd Shift" in cell "(0.7)". Regardless of the time that is
being displayed in "(0.5)" I get "3rd Shift" in cell "(0,7)".
There is apparently some logic error in my code, but I don't see it. Can
anyone lend a hand?
Thanks,
Brian
my code below
=================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")
Target.Offset(0, 6).Value = Format(Date)
If Target.Offset(0, 5).Value >= TimeValue("07:00:00 AM") <
TimeValue("15:00:00 PM") Then Target.Offset(0, 7) = "1st Shift"
End If
If Target.Offset(0, 5).Value >= TimeValue("15:00:00 PM") <
TimeValue("23:00:00 PM") Then Target.Offset(0, 7) = "2nd Shift"
End If
If Target.Offset(0, 5).Value >= TimeValue("23:00:00 PM") <
TimeValue("07:00:00 AM") Then Target.Offset(0, 7) = "3rd Shift"
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub
=================================
Looking for some more help with spreadsheet code. This spreadsheet that I'm
working on is functioning correctly with the exception of cell "(0,7)". When
the time value in cell "(0,5)" is displayed I want to print "1st Shift" or
"2nd Shift" or "3rd Shift" in cell "(0.7)". Regardless of the time that is
being displayed in "(0.5)" I get "3rd Shift" in cell "(0,7)".
There is apparently some logic error in my code, but I don't see it. Can
anyone lend a hand?
Thanks,
Brian
my code below
=================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")
Target.Offset(0, 6).Value = Format(Date)
If Target.Offset(0, 5).Value >= TimeValue("07:00:00 AM") <
TimeValue("15:00:00 PM") Then Target.Offset(0, 7) = "1st Shift"
End If
If Target.Offset(0, 5).Value >= TimeValue("15:00:00 PM") <
TimeValue("23:00:00 PM") Then Target.Offset(0, 7) = "2nd Shift"
End If
If Target.Offset(0, 5).Value >= TimeValue("23:00:00 PM") <
TimeValue("07:00:00 AM") Then Target.Offset(0, 7) = "3rd Shift"
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub
=================================