The syntax should be:

If Target.Offset(0, 5).Value >= TimeValue("07:00:00 AM") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00 PM") _
Then Target.Offset(0, 7) = "1st Shift"

I've split the line to avoid awkward line-wrap. Similar changes are
needed in the lines for 2nd and 3rd shifts.

Hope this helps.

Pete

Sometimes using if/then/elses gets to be pretty confusing.  And "Select Case"
comes to the rescue.  And putting them in a nice order makes it work nicer, too:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim myTime As Date
   Dim myStr As String

   On Error GoTo errHandler
   With Target
       If .Cells.Count = 1 Then
           If Intersect(.Cells, Me.Range("A:A")) Is Nothing Then
               'do nothing
           Else
               Application.EnableEvents = False
               If .Value = "" Then
                   .Offset(0, 5).Resize(1, 3).ClearContents
               Else
                   myTime = Time

                   With .Offset(0, 5)
                       .Value = myTime
                       .NumberFormat = "hh:mm"
                   End With
                   
                   With .Offset(0, 6)
                       .Value = Date
                       .NumberFormat = "mm/dd/yyyy"
                   End With
                   
                   myStr = ""
                   Select Case myTime
                       Case Is < TimeSerial(7, 0, 0), _
                            Is >= TimeSerial(23, 0, 0)
                           myStr = "3rd Shift"
                       Case Is < TimeSerial(15, 0, 0)
                           myStr = "1st Shift"
                       Case Is < TimeSerial(23, 0, 0)
                           myStr = "2nd Shift"
                   End Select
                   .Offset(0, 7).Value = myStr
               End If
           End If
       End If
   End With
   
errHandler:
   Application.EnableEvents = True
End Sub