Excel 2013 - combine conditional formatting

Question

Excel 2013, Windows 7 Pro. I have a spreadsheet with some Conditional formatting, but I need multiple rules & I don't know how to make them work correctly together.

1) First, I want one specific cell filled with Orange if another specific cell in that row contains "YES".

2) Once data is later entered into the Orange cell, the Orange fill color should be removed and -

3) If the data entered in that cell doesn't exactly match the contents of another specific cell in the same row, change fill color to RED.

I've been able to set up 1 & 3, but I don't know how to remove the Orange fill in #2 once I've input data in that cell. What I currently have "works" but not well, since rules 1 & 3 are in conflict with each other. If I make a change to either rule & select "Apply", Excel says "Cannot make changes to the conditional formatting".

Answer 1

Select the column B where you have to apply the conditional formatting using a formula, you can write the following formula in conditional formatting, format values where this formula is true:

=AND(ISBLANK(B2),C2="Yes")

will apply the orange format, and...

=AND(NOT(ISBLANK(B2)),B2<>D2)

will apply the red format.

Where B2 is the column of empty cells and C2="Yes", B2 will be orange and when filled with values different than the value in D2 should be red and with no color if B2=D2.

Answer 2

The rules have a (top to bottom) sequence in the box which implies the sequence they are applied. If a match is found (meaning the test is 'true'), processing stops, and rules further below will not even be looked at.

What you need to do is make up your mind which rules have higher priority, and put those on the top.

The error message you get has probably nothing to do with the rules or the sequence of those rules, but rather with locked cells or merged cells.