validation rule Excel VBA

Question

Hi, I have two columns in a spreadsheet called A1 and A2. The data entered can only be either "Y" or "N". However, A1 and A2 cannot have the same entry for the same record. For example, if the value of A1 is "Y" in record#3, then the value of A2 in record#3 must be "N". and cannot be "Y". Also, if A1 in record#3 is "N", the A2 must be "Y". In othe words, A1 and A2 can NOT have the same entry in the same record. Does it make sense? what will be the best way to do it? using VBA? or using the sheet controls ?

so far, I wrote a simple condition in the Submit_click sub as: if Range (A1).value=Range(A2).value then MsgBox(".....")

but this conflicts with the save function that I have in the Worksheet_beforeClose. When the user closes the worksheet without submittion, a message appears and warning the user that he/she forgot to submit. But the current problem I am facing is, if the user enters "Y" for both A1 and A2, and then he/she closes the worsheet before submitting, the warning message (do you want to submit?) is displayed to the user and as soon as the user responds by "Yes", the other validation message is displayed because A1 and A2 have the same value "Y". Then the program just closed without submitting .

Please guide me ,

Answer 1

put this in the Worksheet_Change event. It will correct the values after the cell is edited.

Const c1pos = 1
Const c2pos = 2

Application.EnableEvents = False

Select Case Target.Column
    Case c1pos
        If UCase(Target.Value) = "Y" Then
            ActiveSheet.Cells(Target.Row, c1pos) = "Y"
            ActiveSheet.Cells(Target.Row, c2pos) = "N"
        Else
            ActiveSheet.Cells(Target.Row, c1pos) = "N"
            ActiveSheet.Cells(Target.Row, c2pos) = "Y"
        End If
    Case c2pos
        If UCase(Target.Value) = "Y" Then
            ActiveSheet.Cells(Target.Row, c1pos) = "N"
            ActiveSheet.Cells(Target.Row, c2pos) = "Y"
        Else
            ActiveSheet.Cells(Target.Row, c1pos) = "Y"
            ActiveSheet.Cells(Target.Row, c2pos) = "N"
        End If
End Select

Application.EnableEvents = True