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

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

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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.

Maybe like this:

Please Login or Register  to view this content.

Thankyou that works great

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

Please Login or Register  to view this content.

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

any ideas
thanks