0

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".

2 Answers 2

3

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.

0
-1

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.

2
  • Rules only stop if Stop If True is checked on the rule. By default, all rules are applied in order.
    – NetMage
    Sep 19, 2018 at 19:42
  • yes, but it makes no sense (and wastes time) to have the first rule color the cell and then the second rule re-color it. If multiple rules modify the same property (like 'field color'), it is most efficient to sort them by desired priority, and check the 'stop' flag.
    – Aganju
    Sep 19, 2018 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.