1. Sheet1に時間を入力する範囲があります。これを範囲限定するのに、複数の方法があります。
まず、 Intersectメソッドで「ある範囲Targetが範囲Range("D4:E34")に入っているかどうか」を判定する方法です。あるいは、範囲を列として指定することもできます。
Intersect(Target,Range(Columns(4), Columns(5))) ,または
Intersect(Target,Range("D:D, E:E"))
Intersect(Target, Columns("D:E"))
Intersect(Target, Union(Columns(4), Columns(5)))
のようにも書けます。きちんと指定したい場合は、範囲に名前をつけておきその名前で指定することもできます。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("範囲につけた名前")) Is Nothing Then
        '実行するコード
    End If
End Sub

2. 次に '実行するコード に書くコードです。
数値の入力ですが、入力するセルはデフォルトの標準としています。
そこに3桁あるいは4桁の数値を入力します。
またIF文を使って、入力された値が数値で、中に「：」が含まれない場合に限定します。
ここで使うのは、IsNumeric関数です。 数値として扱えるかどうかを判定します。
そしてInStr関数でTarget内に引数で指定した：コロンを探して、0であればというその値をユーザー定義で時間に設定します。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D:D,E:E")) Is Nothing Then
        If IsNumeric(Target.Value) And InStr(Target.Text, ":") = 0 Then
        Target.Value = Format(Target.Value, "00:00")
        End If
    End If
End Sub

このコードをSheet1に設定して、数値を入力します。 1800と入力すると、18：00と時間に表示されました。

いかがでしょうか。時間入力の手間を省くイベントです。Worksheet_Changeで書式設定して時間表示するマクロを紹介しました。

3. さらに手を加えて、時間表示のセルの変更の場合をかんがえます。
入力された時間を訂正しようとすると、18：00：00のように時間表示になっています。直接訂正することができます。

4. ただし、一旦入力されたデータを消去して、再入力する方が楽かもしれません。その時にはコードを追加する必要があります。
理由は、3桁4桁の数値で入力するためには、セルの書式が標準である必要があるからです。
もし、 Target.Value = ""ならは、書式を "G/標準" にすると設定します。そして一旦このプロシージャを終了させます。

    If Target.Value = "" Then
        Target.NumberFormatLocal = "G/標準"
    Exit Sub
    End If

時間を3桁4桁数値で入力するイベントのコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "" Then
        Target.NumberFormatLocal = "G/標準"
    Exit Sub
    End If
    If Not Intersect(Target, Range("D:D,E:E")) Is Nothing Then
        If IsNumeric(Target.Value) And InStr(Target.Text, ":") = 0 Then
        Target.Value = Format(Target.Value, "00:00")
        End If
    End If
End Sub