Condition locking of adjacent cells

[CrashBandicoot]

Background:

I have a spreadsheet that works pretty well for tracking events. For each event there are two drop down data validation lists located in column J and column K. The list in column J has values A,B,C. The list in column K has values D,E,F. Each row is one event. For each event, either a response in column j or a response in column k are acceptable. There should never be an event that has both(but users consistently do it anyway). The spreadsheet is available to 20+ people and rows are constantly added.

Need help with:


If say J2 has a value, I'd like to lock the adjacent cell k2.
If say k2 has a value, I'd like to lock the adjacent cell j2.


Tried:

ive tried some conditional formation or using is blank formulas. The closest I came was using an isblank formula and a data validation list. I could choose options on the list and it would lock the corresponding cell but I couldn't get the list to actually show which wouldn't work. I think with VBA this would be a cinch but I'm not sure. I'm only slightly proficient at frankensteining code. I'd take any help I can on this.

 

[onlyadrafter]

Hello,

the following code needs to go into the relevant sheet code window

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 10 Then
        If Not (IsEmpty(Target.Offset(0, 1).Value)) Then
            Target.ClearContents
        End If
    End If
    If Target.Column = 11 Then
        If Not (IsEmpty(Target.Offset(0, -1).Value)) Then
            Target.ClearContents
        End If
    End If
    Application.EnableEvents = True
End Sub

This will not allow data in both columns J and K of the same row and will just clear the contents.

Is this method acceptable?

 

[CrashBandicoot]

Hey onlyadrafter,

First thanks for tying to help out!

Second, it almost works. When I used the code it still allowed you to select two options. It wasn't until you activated one of the cells in the row that it cleared one of the two cells. Would there be a way to have it automatically clear whatever you tried to put in second if one of the columns already had data?

Clearing contents as opposed to locking is totally fine though.

Edit: a thought just occurred to me. If it's easier:
- if text in both adjacent columns a message box alerting them to issue appears, user dismisses and fixes, if the try and leave the active row without fixing they get another message box. Once one cell deleted they can move on.

I sincerely appreciate the help, this has been driving me nuts.