How to setup Time-Card Data Validation template?

[Macro_Nerd99]

I have a workbook with a Time Tracker Template for work, and I want to add data validation to it to make sure the times are calculated correctly.
For example, if an end time is before a start time, the cell turns red or gives a warning message. But also, if a start time is before the previous end time, a cell turns red.

I tried creating a large if statement in the worksheet cell change event where I loop through each row making sure it highlights any violations red, but then when it moves to the next row, it highlights it back to blue. How do you recommend fixing this?

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Worksheets("Time Tracker Template")
 Dim start_time As Date
 Dim End_time As Date
 Dim Start_time_violation As Integer
 Dim End_Time_Violation As Integer
 Dim start_time_range As Range
 Dim end_time_range As Range
 Dim next_start_time_range As Range
 
 End_Time_Violation = 0
 Start_time_violation = 0
 
     With ws
        For i = 2 To 100
             start_time = Range("A" & i).Value
             End_time = Range("B" & i).Value
             Set next_start_time = ws.Range("A" & i + 1)
              Set start_time_rng = ws.Range("A" & i)
              Set end_time_rng = ws.Range("B" & i)
              Set next_start_time_rng = ws.Range("A" & i + 1)
             
             
             
             If start_time > 0 And End_time > 0 And next_start_time > 0 Then
                    If start_time < End_time And End_time < next_start_time Then
                        start_time_rng.Interior.Color = 16764057
                        end_time_rng.Interior.Color = 16764057
                        next_start_time_rng.Interior.Color = 16764057
                    Else
                       If start_time > End_time Then
                        start_time_rng.Interior.Color = 16764057
                        end_time_rng.Interior.ColorIndex = 3
                        End_Time_Violation = End_Time_Violation + 1
                        
                       ElseIf End_time > next_start_time Then
                        end_time_rng.Interior.Color = 16764057
                        next_start_time_rng.Interior.ColorIndex = 3
                        Start_time_violation = Start_time_violation + 1
                       Else
                            
                       End If
                    End If
                         
                ElseIf next_start_time = 0 Then
                    If start_time < End_time Then
                        start_time_range.Interior.Color = 16764057
                        end_time_range.Interior.Color = 16764057
                    Else
                        
                     End If
                End If
                
        
             
             Next i
          End With
End sub

 

[Macro_Nerd99]

Sorry, but I have one more little request for this post.
As part of the Data Validation, where could I put a check to make sure the cell is in format: mm/dd/yyyy h:mm upon changing as well.

and if the cell is changed, and isn't in that format, the cell turns red and displays a msgbox.
Thanks

 

[NoSparks]

Not sure I follow what you're after here, but seeing you've posted this as a separate question I'll watch and see what you get for suggestions.