Merging code - Validation and Formatting

I have Some Formatting that puts a ":" between 0000 to make a time 00:00

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("c7:g37")) Is Nothing Then Exit Sub
     
     With Target
         
         vVal = Format(.Value, "0000")
          If IsNumeric(vVal) And Len(vVal) = 4 Then
            Application.EnableEvents = False
            .Value = Left(vVal, 2) & ":" & Right(vVal, 2)
            .NumberFormat = "[h]:mm"
          End If
    End With
           
     Application.EnableEvents = True

The above is the code.

I also want to apply some validation to the same range. However, the validation depends upon the Formatting. So I want the formatting to run first I have tried putting the validation in the code before the formatting but to no avail.

here is the validation and the formatting

Private Sub Worksheet_Change(ByVal Target As Range)
Range("D7").Select
    With Selection.validation
        .Delete
        .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:= _
        xlGreater, Formula1:="=C7"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Dim vVal
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A7:g37")) Is Nothing Then Exit Sub
     
     With Target
         
         vVal = Format(.Value, "0000")
          If IsNumeric(vVal) And Len(vVal) = 4 Then
            Application.EnableEvents = False
            .Value = Left(vVal, 2) & ":" & Right(vVal, 2)
            .NumberFormat = "[h]:mm"
          End If
    End With
           
     Application.EnableEvents = True
End Sub

The above validation wont work but the formatting does.

Alternatively,


I have just found that the validation wont work unless the information is manually entered into the cell, the result of a calculation slips by undetected. Is there anyway i can chage this.

Thanks in advance

Just found this out on the internet somewhere

"Note that Validation works only on direct user input. It does not work with data that is pasted in to a range, values that are the result of calculations, or cell modification by Visual Basic code."

Simon Green