Pop Up message when value is changed - dependent on other conditions

[emw2368]

My data starts in row 2.
Column AA is validated for Y,N,N/A values only, column ABcontains N/A or a date, and column AD can contain N/A or a date.
All columns will initially be ‘N/A’ but there is apossibility that this will change, is there any formula that will bring up apop-up message if AD is N/A and AB is changed from N/A to a date?
NB: The N/A value will already be in AD, I need the pop upto come up upon entry of the date in AB.
I’ve tried =AND(AA2="Y", AD2="N/A")custom data validation in column AB but unfortunately this does not work. Any adviceon this or alternatives would be greatly appreciated.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a date in column AB and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("AB:AB")) Is Nothing Then Exit Sub
    If IsDate(Target) And Target.Offset(0, 2) = "N/A" Then
        MsgBox ("Your message here.")
    End If
End Sub