VBA help: alert if end date entered is earlier than start date range

[Lil Stinker]

Hey guys,
I'm looking for a way to highlight a date error when a user enters an end date that is earlier than the start date.
My end date is located in C23. My start date range is in F16:F26.
I currently use the following to highlight the incorrect date if the date entered in any of the cells in the start date range is incorrect:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

If Not Intersect(Target, Range("F16:F26")) Is Nothing And IsDate(Target) = True Then
    If Target.Value > Range("C23").Value Then
        Target.Font.Color = RGB(255, 0, 0) 'no good, red font
        Range("C23").Font.Color = RGB(255, 0, 0)
        MsgBox "Billing End Date cannot occur before Billing Start Date.", vbExclamation, "Check ya dates!"
    Else
        Target.Font.Color = RGB(0, 0, 0) 'all good, black font
        Range("C23").Font.Color = RGB(0, 0, 0)
    End If
End If

This seems to work as long as the incorrect date is entered anywhere in the start date range but not if the incorrect date is entered in the end date cell. How can I change the end date font color and throw in a msgbox if the incorrect date is entered in the end date cell based on any one of the dates in the start date range?

 

[Micron]

if the incorrect date is entered in the end date cell

I can't really picture what that means. Assuming the end date could change or need to be edited then wouldn't the "wrong" dates then be in the F range and they would need to be fixed? Besides, based on the first part, the end date is already in C23 and the others are compared to it, so how could C23 be wrong?

Maybe what you need is another IF block like the first one, but base what needs to happen on
If Not Intersect(Target, Range("C23")) Is Nothing...

 

[Peter_SSs]

Try this code in place of your code above.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  
  If Target.CountLarge > 1 Then Exit Sub
  
  If Not Intersect(Target, Range("F16:F26")) Is Nothing And IsDate(Target.Value) Then
    If Target.Value > Range("C23").Value Then
      Target.Font.Color = RGB(255, 0, 0) 'no good, red font
      Range("C23").Font.Color = RGB(255, 0, 0)
      MsgBox "Billing End Date cannot occur before Billing Start Date.", vbExclamation, "Check ya dates!"
    Else
      Target.Font.Color = RGB(0, 0, 0) 'all good, black font
      Range("C23").Font.Color = RGB(0, 0, 0)
    End If
  End If
  
  If Not Intersect(Target, Range("C23")) Is Nothing Then
    If Target.Value < Application.Max(Range("F16:F26")) Then
      Target.Font.Color = RGB(255, 0, 0) 'no good, red font
      For Each c In Range("F16:F26")
        If Target.Value < c.Value Then
          c.Font.Color = RGB(255, 0, 0)
        Else
          c.Font.Color = RGB(0, 0, 0)
        End If
      Next c
      MsgBox "Billing End Date cannot occur before Billing Start Date.", vbExclamation, "Check ya dates!"
    Else
      Range("F16:F26").Font.Color = RGB(0, 0, 0) 'all good, black font
      Target.Font.Color = RGB(0, 0, 0)
    End If
  End If
End Sub

 

[Lil Stinker]

Success! Thank you, Peter. You guys really know your stuff!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.