How to loop a Vlookup Function until result is not empty?

Question

I am looking for a way to repeatedly search through a date table with events.

The user will select a start date and an end date and I need to know if any of these dates contain an event.

The worksheet lists all the dates between the end date and the start date. I need to search through this array.
Front end view

The search area is a table in another sheet in the workbook looking like this:
Date Table

I want the macro to search through column A for the dates in the list and return a msgbox if any of the dates correspond to an event in column E.

This is what I have so far. I am stuck on how to have SearchDate as a variable range for my vlookup, and also how to stop the loop once it has found one result, as this will be enough to prompt the warning message.

Sub EventFinder()
Dim RowNMBR As Long
Dim SearchDate As Range

RowNMBR = 4
Set SearchDate = Cells(4, 12)

With SearchDate
    For Each c In Range("L5:L33")
        On Error Resume Next

        RowNMBR = RowNMBR + 1
        Set SearchDate = Cells(RowNMBR, 12)   

        If Not Application.WorksheetFunction.VLookup(SearchDate, Sheets("Forecast").Range("A:E"), 5, False) = "" _
          Then MsgBox "There is an Event on these dates, contact the Revenue Manager!", vbOKOnly, "Event Warning"
        Exit Sub ' and exit procedure
    Next c
    On Error GoTo 0
End With

End Sub

To add to the macro I created an automated macro to call my macro whenever the value of "DoA" or "Nights" changes. This does not work as it should.

I unprotected the sheets and workbook for as long as I am working on it and it still does not work.

PROBLEM IS FIXED WITH CODE BELOW

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim intersection As Range
' Target => you already have an address of changed cell(s)

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("E6")

' Application.Intersect - returns a Range object that represents the
' rectangular intersection of two or more ranges.
Set intersection = Application.Intersect(KeyCells, Target) ' if it intersects that the range will be initialized

If Not (Target.Rows.Count > 1 And Target.Columns.Count > 1) Then    ' check that changed range has only 1 cell
                                                                    ' because if you select a 6th row
                                                                    ' and clear it's contents (or change in any other way) -
                                                                    ' the event will be triggered as well

    If Not intersection Is Nothing Then     ' if the intersection range is initialized
                                            ' then event will be triggered
        Call EventFinder
    End If
End If

Set KeyCells = Range("E9")

' Application.Intersect - returns a Range object that represents the
' rectangular intersection of two or more ranges.
Set intersection = Application.Intersect(KeyCells, Target) ' if it intersects that the range will be initialized

If Not (Target.Rows.Count > 1 And Target.Columns.Count > 1) Then    ' check that changed range has only 1 cell
                                                                    ' because if you select a 6th row
                                                                    ' and clear it's contents (or change in any other way) -
                                                                    ' the event will be triggered as well

    If Not intersection Is Nothing Then     ' if the intersection range is initialized
                                            ' then event will be triggered
        Call EventFinder
    End If
End If

Set KeyCells = Range("E12")

' Application.Intersect - returns a Range object that represents the
' rectangular intersection of two or more ranges.
Set intersection = Application.Intersect(KeyCells, Target) ' if it intersects that the range will be initialized

If Not (Target.Rows.Count > 1 And Target.Columns.Count > 1) Then    ' check that changed range has only 1 cell
                                                                    ' because if you select a 6th row
                                                                    ' and clear it's contents (or change in any other way) -
                                                                    ' the event will be triggered as well

    If Not intersection Is Nothing Then     ' if the intersection range is initialized
                                            ' then event will be triggered
        Call EventFinder
    End If
End If

End Sub

Answer 1

Check this out. Read comments and don't forget to put proper sheets' names to certain lines.

Sub EventFinder()

Dim shtSource As Worksheet
Dim shtData As Worksheet
Dim SearchDate As Range
Dim searchRange As Range
Dim dataRange As Range
Dim s As Range, d As Range

Set shtSource = ThisWorkbook.Sheets("") ' put proper sheets' names
Set shtData = ThisWorkbook.Sheets("")   ' and here also


Set searchRange = shtData.Range(shtSource.Cells(1, 1), shtSource.Cells(Rows.Count, 1).End(xlUp)) ' set the range of dates to look for each of below in
Set dataRange = shtSource.Range(shtSource.Cells(5, 12), shtSource.Cells(Rows.Count, 12).End(xlUp)) 'set range of dates to look for 


For Each d In dataRange 'take each date from source
    For Each s In searchRange ' check every source date in data range
        If s.Value = d.Value Then ' if dates match
            If Not s.Offset(0, 4) = "" Then ' if event is not empty
                ' show message:
                MsgBox "There is an Event on these dates, contact the Revenue Manager!", vbOKOnly, "Event Warning"
                Exit Sub ' and exit procedure
            End If
        End If
    Next
Next
End Sub

UPDATE 1

First of all see my answer to this post and fix your settings. Second thing is that I see that you're trying to break your task into parts and ask as different questions - that's not always good. Third one - is that you'd better show your "Front end view" with column names and rows numbers, as like on the second screenshot.

Then, check below your updated code with my comments (will be better to copy it to your IDE and see it there and there's more comments that code :))

Sub EventFinder()
Dim RowNMBR As Long
Dim SearchDate As Range

RowNMBR = 4     ' you've assigned a row number
                ' that's not the best solution,
                ' as your start row is actually 5
                ' see * comment in the loop regarding this

Set SearchDate = Cells(4, 12)   ' you've assigned a range on active sheet (which one?) to a variable
                                ' BUT see ** comment in the loop

With SearchDate ' useless statement 'cos there's nothing that uses "With" statement below

    For Each c In Range("L5:L33") ' an error should occur here if you read the link and setup properly 'cos you didn't declare the "c" variable
    On Error Resume Next    ' sometimes you can't avoid using this statement, but not this time
                            ' this time it only harms you

    RowNMBR = RowNMBR + 1   ' * it's better to assign the start value at the top as 5, and move this line just before the "Next c"
    Set SearchDate = Cells(RowNMBR, 12) ' ** you re-assign this variable on each loop iteration, so first assignment is useless


        ' Your question why does it always exit the sub. See YOUR block of code:
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        If Not Application.WorksheetFunction.VLookup(SearchDate, Sheets("Forecast").Range("A:E"), 5, False) = "" _
        Then MsgBox "There is an Event on these dates, contact the Revenue Manager!", vbOKOnly, "Event Warning"
        Exit Sub ' and exit procedure
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' and check out following:
        ' these two lines (your code):
'    If Not Application.WorksheetFunction.VLookup(SearchDate, Sheets("Forecast").Range("A:E"), 5, False) = "" _
'    Then MsgBox "There is an Event on these dates, contact the Revenue Manager!", vbOKOnly, "Event Warning"

        ' are the same as this one (you just added a linebreak with the "_" sign ):
'    If Not Application.WorksheetFunction.VLookup(SearchDate, Sheets("Forecast").Range("A:E"), 5, False) = "" Then MsgBox "There is an Event on these dates, contact the Revenue Manager!", vbOKOnly, "Event Warning"

        ' so the "Exit Sub" statement is reached everytime,
        ' because if you put it into one line it looks like "If [condition = true] then [do something (just 1 statement) and end of conditional check]"
        ' and Exit statement is not in that If block and performed anyway

        ' the thing you need is below
'    If Not Application.WorksheetFunction.VLookup(SearchDate, Sheets("Forecast").Range("A:E"), 5, False) = "" Then  ' "If [condition = true] then
'        MsgBox "There is an Event on these dates, contact the Revenue Manager!", vbOKOnly, "Event Warning"         ' [do something (first statement)
'        Exit Sub                                                                                                   ' (2d statement)
'                                                                                                                   ' (other statements if needed)
'    End If                                                                                                         ' and end of conditional check]"

    Next c
    On Error GoTo 0
End With

End Sub

UPDATE 2

The problem is in improper use of Target object. This is a range object and in the line Target.Range("E6") you are trying to reach Target's E6 cell, I hope the picture clarifies what I mean:

The Target holds the address of changed cell(s), not Worksheet's, so, basically, this is all you need:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' Target => you already have an address of changed cell(s)

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("E6")


If Not (Target.Rows.Count > 1 And Target.Columns.Count > 1) Then    ' check that changed range has only 1 cell
                                                                    ' because if you select a 6th row
                                                                    ' and clear it's contents (or change in any other way) -
                                                                    ' the event will be fired as well

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then  ' and you need to check whether the changed cell is
                                                                    ' the one that will fire an event
        Call EventFinder
    End If
End If
End Sub

The use of Protect/Unprotect depends on whether you need to change this particular protected sheet and doesn't affect on Call EventFinder, so use it if needed.

UPDATE 3

Check out this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim intersection As Range
' Target => you already have an address of changed cell(s)

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Union(Range("E6"), Range("E9"), Range("E12"))


' Application.Intersect - returns a Range object that represents the
' rectangular intersection of two or more ranges. (c)Excel Help, put cursor on the keyword and press F1
Set intersection = Application.intersect(KeyCells, Target) ' if it intersects that the range will be initialized


If Not (Target.Rows.Count > 1 And Target.Columns.Count > 1) Then    ' check that changed range has only 1 cell
                                                                    ' because if you select a 6th row
                                                                    ' and clear it's contents (or change in any other way) -
                                                                    ' the event will be triggered as well

    If Not intersection Is Nothing Then     ' if the intersection range is initialized
                                            ' then event will be triggered
        Call EventFinder
    End If
End If
End Sub