Confusion with Conditional Formatting Order

MikeMcCollister

Board Regular
Joined
May 6, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
I've been using conditional formatting in Excel for years and I was pretty sure that I had an understanding of the order that the rules were applied. However, I am confused based on the text "Rule (applied in order shown)". I figured that the rules would be applied from top to bottom. Here is a screen shot of what I am talking about with some very simple formula-based rules.

1606939142332.png

I thought that as shown in the screen capture that it would first check if the value in the cell was > 4 and make the background green then go to the next rule and if the cell value was greater than 2 then it would paint it blue. In that case there would be no green cells.

So, this looks like the order of the rules is from the bottom up. Is that the case?

Thanks,

Mike
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
The first rule has the highest priority & will take precedence if any other rules conflict.
If the rules don't conflict both will happen
+Fluff v2.xlsm
A
11
22
33
44
55
66
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A6Expression=A1>4textNO
A1:A6Expression=A1>2textNO
 

MikeMcCollister

Board Regular
Joined
May 6, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Fluff,

Thanks. I changed my rule so that it matches your where A1>4 the text is red and for A1>2 the background is blue, as shown with the screen capture. It works as I expect. In fact, no matter what order I make the rules it works the same, as I would expect in this situation. However, once I set the background color, it favors the first one as you stated. Is that what you mean by "conflict"? If so, it looks like there is a "stop of conflict" built-in. Am I understanding this correctly?

1606941582105.png

Thanks,

Mike
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Is that what you mean by "conflict"?
Yes that's right. If you are trying to change the fill colour in both rules the first rule will work as it has the highest priority (you obviously cant have two fill colours in one cell).
However if the formats do not conflict (ie fill colour on one & font colour on the other) both rules will work unless you check "Stop if True"
 
Solution

MikeMcCollister

Board Regular
Joined
May 6, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Fluff,

Thanks for the clarification. I just figured it would apply rule 1 then rule 2 but I know better now.

You have been most helpful.

Regards,

Mike
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,977
Messages
5,599,151
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top