Check Sheet2!B6, that is where I have put the conditional formatting. the reason why I need the conditional formatting (or anything that can work, for that matter) is because what you see in sheet2 are the different configuration of the airplane. If Position A is taken, position AA, AB, ABR and ABR are not available anymore. I do have more positions that I have to close.
1. Are we talking Conditional Formatting or Data validation?
2. Whichever it is, when you're using a formula, it must equate to True or False; with Data Validation, False if it's invalid, True if it's valid. With Conditional formatting, True to get formatted and False not to get formatted.
3. Cell B6 is the only cell on the sheet with Data Validation; its formula:
if(or(B12>0,B18>0,B18>0,B24>0,B28>0)
is not a valid formula anyway. While developing such a formula, it's a good idea to put the formula in a cell on the same sheet somewhere to make sure that it's a valid formula and that it's returnng True/False. Once you have a successful formula, transfer it to the cell's Data Validation or Conditional Formatting and delete it from the cell you developed it in.
4. Data Validation is for validating data
in the same cell, so B6 Data Validation would only control what goes in B6 and nowhere else.
5. Since you're using automation, it's probably easier to use Conditional Formatting throughout (you're right, Data Validation seems to be being ignored when a cell's value is changed by code). The sort of Conditional Formatting formula you could use in B6 to check that all of the cells B12, B18, B24 and B28 are empty is:
=COUNTA(B12,B18,B24,B28)>0
If they are all empty this will be False, if not it will be True.
Or better:
=AND(LEN(B6)>0,COUNTA(B12,B18,B24,B28)>0)
which will only highlight the cell if there's something in B6 AND there is something in one or more of the other 4 cells.
Because you can have multiple conditional formats in a cell, you can also shade cells such as B12, B18 etc. as soon as data is arrives in B6, to signal to the user that that space is not available; in B12 you could have conditional formatting formula of the ilk:
=COUNTA(B6)>0
This would not prevent data entry into B12 but if you were to add a further, different conditional format formula with different formatting to B12 such as:
=COUNTA(B6,B12)=2
with a format to alert the user there's an error, then the user can make a choice as to which cell needs to put right (B12 or B6) and the formatting will change accordingly. Of course, adjustments made here by the user will not appear on the other sheet! That's a whole new ball-game.
The conditional formatting on the sheet can become quite complex but with good planning could become very useful.
Also... can I use more conditional formatting for the same cell? I need to limit the max weight for each cell in the sheet.
Yes, you can use many conditional formats for the same cell (as suggested above).