VBA to Cause One Range to Lock if There is Data in Another Range

[effingeh]

Hello! Is it possible to lock the cells in one range (D13:E22) if there is any non-zero data in another (B13:C22) and vice-versa?

The sheet defaults to all 40 of these cells being editable and at $0. There is also VBA being used to ensure that when data is deleted, it defaults back to $0. There is a 5th column that is looking at all 4 columns and taking B-C+D+E, but that is because users should only use B and C or D and E. The code would help prevent any issues in accidental entries that use more than the 2 columns that are needed.

Also - Would this be easier/possible with data validation? I attempted this and was able to get it to work on one half (an error would pop up asking you to delete out the other 'side'), but I wasn't able to duplicate it when I attempted to set up the same rules for the other side.

Thanks in advance!

 

[iggydarsa]

This is how I was able to do it:
- Select B13:C22
- Click on Data > Data Validation > Data Validation
- Under settings "Allow" = Custom and "Formula": =AND($D13="", $E13="")
- Make sure "Ignore Blank" is unchecked

Do the similar thing for the second half:
- Select D13:E22
- Click on Data > Data Validation > Data Validation
- Under settings "Allow" = Custom and "Formula": =AND($B13="", $C13="")
- Make sure "Ignore Blank" is unchecked

 

[iggydarsa]

Just realized you use 0 instead of blank cells, so you might wanna change the formula above by replacing double quotes ("") with zero (0)

 

[effingeh]

thanks, iggydarsa! that works perfectly when data is in the same row. is there a way to update the formula to have this look at the entire range? for example, if data is in B13, it would close off all of D13:E22?

i tried using a range in place of the cells and even changing the logic to look at the sum of the range and make sure it was $0, but the only thing that worked was adding all 20 cells individually to the AND statement (=AND($D$13=0,$D$14=0,$D$15=0,$D$16=0,$D$17=0,$D$18=0,$D$19=0,$D$20=0,$D$21=0,$D$22=0,$E$13=0,$E$14=0,$E$15=0,$E$16=0,$E$17=0,$E$18=0,$E$19=0,$E$20=0,$E$21=0,$E$22=0). any thoughts on how to make that a little more elegant?

thanks again for the initial help!

 

[iggydarsa]

Change =AND($D13="", $E13="") to =AND($D$13:$E13="")

Change =AND($B13="", $C13="") to =AND($B$13:$C13="")