verifying times

[robertvdb]

I have a sheet where I input the start and end time of an activity.

These times are inputted in the "Custom 0\:00" format to allow easy entry. For instance, when I type 0605 then 06:05 automatically appears.

Now I need to ensure that the end time is later than the start time. I want VBA to do this.

The only way I think is possible is to first convert 0605 to a numerical value = 6 hours and 5 minutes = 365 minutes.

Do the same with the end time, and compare both values.

But how to write this in VBA ?

 

[kvsrinivasamurthy]

This can be done by Data Validation.
Select C2:C4
Data --> Data valdation
Allow: Custom (select)
Formula: Enter formula C2>B2
Ok.

 

[robertvdb]

Thanks but I really need it in VBA

 

[iggydarsa]

You wont need to convert.

Sub ValidEntry()
    Dim lr As Long: lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    
    For i = 2 To lr
        If Range("C" & i).Value <= Range("B" & i).Value Then
            Range("D" & i).Value = "End time must be later than start time"
        End If
    Next i
End Sub

 

[kvsrinivasamurthy]

Worksheet event can be used. Range C2:C4 can be changed as required.
Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Application.EnableEvents = False
Set R = Range("C2:C4")
If Not Intersect(Target, R) Is Nothing Then
If Target <= Target.Offset(0, -1) Then
Target = ""
MsgBox ("Time should be more than " & Format(Target.Offset(0, -1), "hh:mm"))
End If
End If
Application.EnableEvents = True
End Sub

How to use workheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm