Clear Cell After Messagebox

I have a grid of cells that evaluate to "yes" (true) or "no" (false)

Upon entering a name in a particular range of cells, J7:J165, if a particular condition evaluates to true, I would like a message box to appear, warning of the error, and then clear the name out of the cell and keep the focus on it.

It would look something like:

a name gets picked from a drop down menu in cell J7

One of the conditions would be in cell J172. If it's "no", nothing happens, if it's true, then how I currently have it set up is that it calls a public function, for instance, availabilitycheck(),....the formula in cell J172 is:

=IF(OR(Schedule!B7<VLOOKUP(J7,EmpInfo!$C$3:$V$200,7,FALSE),((IF(F7="close",(MOD($AE$4-B7,1))*24,(MOD(F7-B7,1))*24))>=MOD(VLOOKUP(J7,EmpInfo!$C$3:$V$200,8,FALSE)-Schedule!B7,1)*24)),availability(),"no")

The function contains the message box, and it is here I would like to also be able to clear the contents of the selected cell.

I had a similar question not too long ago, and the solution was:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange As Range
    Set myrange = Range("J7:J165")
    If Not Intersect(Target, myrange) Is Nothing Then
        Application.EnableEvents = False
        If WorksheetFunction.CountIf(myrange, Target) > 1 Then
            MsgBox "This employee has already been scheduled during the Friday matinee shift.", vbExclamation + vbOKOnly, "Duplicate Shift"
            Target = ""
            Target.Activate
        End If
        GoTo exit_sub
    End If

Set myrange = Range("Z7:Z165")
    If Not Intersect(Target, myrange) Is Nothing Then
        Application.EnableEvents = False
        If WorksheetFunction.CountIf(myrange, Target) > 1 Then
            MsgBox "This employee has already been scheduled during the Friday evening shift.", vbExclamation + vbOKOnly, "Duplicate Shift"
            Target = ""
            Target.Activate
        End If
    End If
exit_sub:
    Application.EnableEvents = True
End Sub

I also experimented with this, not as a public function, obviously......

I tried substituting Vlookup in place of the worksheetfunction.countif, and taking out the function call in the excel vlookup formula and replacing with "yes" (as the vlookup formula would evaluate to "yes" or "no", but I could not get the vba code to work.

In the vb code, to locate the cell in question that would potentially throw up the messagebox, the vlookup expression would take the value in the cell that the name was dropped in, then check it against the range B172:Q330, the cell that contains the true/false value would be 9 columns over.....the range is greater than 9 columns because I would just use this a constant range, just change the # of columns where necessary.

I thought maybe the fact that the "cell" that the name gets dropped in was actually a collection of merged cells was the culprit, but the code I included here from a previous response works on the same range, and does exactly what I want to do in this situation.

I explain things horribly, so +rep if you even understand what I just wrote......