Prevent data entry in cell A1 if there is data in a cell B1 and vice versa

[westonkw]

I need to prevent the user from entering data in cells A1 & B1 on the same row. They need to use one or the other. For instance on row 1 they may need to use A1 therefore they cannot use B1, then on row 2 they may need to use B2 therefore they cannot use A2. If they use both cells on the same row the formulas dont work properly in other cells dependent on these cells. I need it to work like this all the way down the spreadsheet.

Thanks for the help

[Richard Buttrey]

Hi,

One way would be the following worksheet Selection_change event macro. It puts a null value in an A or B cell of the B or A adjacent cell has a value. If you want a zero value rather than null just change the

Range("A" & Target.Row) = ""

to

Range("A" & Target.Row) = 0

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A:B")) Is Nothing Then
    If Range("A" & Target.Row) = Target Then
        If Range("B" & Target.Row) <> "" Then
            Range("A" & Target.Row) = ""
        End If
    End If
    
    If Range("B" & Target.Row) = Target Then
        If Range("A" & Target.Row) <> "" Then
            Range("B" & Target.Row) = ""
        End If
    End If


End If
End Sub

[westonkw]

Thanks for the response, but that only kind of works. I dont know if it has anything to do with it but I have a valildation list in those to cells. If I select data in the A cell, it will still allow me to select data from the list in the B cell, and then which ever cell A or B I click on next it will remove the data from that cell. If I dont click on either cell it will keep data in both cells.

[shg]

Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Column > 2 Then Exit Sub
        If IsEmpty(.Value) Then Exit Sub
        Application.EnableEvents = False
        .Offset(, IIf(.Column = 1, 1, -1)).ClearContents
        Application.EnableEvents = True
    End With
End Sub

[westonkw]

Thankyou that works great

[westonkw]

This code works fine on the sheet until I try to add a page. When I say "add a page" I mean there is a macro that adds an additional page to this sheet, when the user runs out of lines, they click and add page button and it adds another page to the sheet.
When you try to add a page it gives a:

"Runtime Error"
"Application-defined or object defined error"

Then if you debug it it takes you to this code and highlights in yellow the line
.Offset(, IIf(.Column = 1, 1, -1)).ClearContents

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Column > 2 Then Exit Sub
        If IsEmpty(.Value) Then Exit Sub
        Application.EnableEvents = False
        .Offset(, IIf(.Column = 1, 1, -1)).ClearContents
        Application.EnableEvents = True
    End With
End Sub

Im an amatuer at best, when it comes to VBA code sometimes I can understand and work my way through some of this stuff, but this line has me baffled.

I would like to attach the file but even zipped it is to large to attach.
Thanks for all the help