Shift starts at 14:30 driving route T


"Rick Rothstein (MVP - VB)" wrote in
message ...
Can you show us some samples of the data that could be in cell U23?

Rick


"Patrick C. Simonds" wrote in message
...
Cell U23 has both text and time in it. What I was hoping was that the
code below would change the text to all caps and the number to a time
format.


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "'00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

ErrHandler:

Application.EnableEvents = True

End Sub