Hi my friend,

Here is what I came up with:
Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Count = 1 And Target.Column < 3 Then
    With Target
      If .Value Like "#" Or .Value Like "##" Or .Value Like "####" Then
        If .Value < 2400 Then
          Application.EnableEvents = False
          .Value = TimeSerial(Int(.Value / 100), (.Value Mod 100), 0)
          Application.EnableEvents = True
          Target.NumberFormat = "hh:mm"
        End If
      End If
    End With
  End If
  
End Sub
You might want to take a gander at one of our KB entries here

Hope that helps?

Mark