Calc: How to change conditional formatting priority

English
, , , ,
#4

cf_overlap
cf_overlap750×561 19.4 KB

There are different ranges that both include B2 in the example file. The rule highlighting a row depending on C cell doesn’t affect B2, because the rule affecting B column is created first. With multiple overlapping formatting rules, it becomes very unpractical to keep deleting and adding back the rules just to change priority.

Weird thing I noticed is, the formatting rules will execute in the order that they are added, but they will be listed alphabetically anyway. This makes even reading the priority from the list impossible.

#5

Meh, my existing bugzilla account didn’t work on that website. Seems like I have actually found a bug with reading the priority from the list (I said it in another comment), but I have already registered to this website for this problem. I have bugzilla account too. I think I’ll skip adding 1 to a year-old request’s CC list if it’s that hard and that hopeless/slow.

#6

Unfortunately, there was nothing in the file. I recreated it but this time with two sheets with different ordering of precedence; on one sheet the range B1:B3 conditions were done first, the second the reverse is true.
cf_overlap.ods (10.1 KB)

When I open Format > Conditional > Manage the condition with precedence is highlighted even if it is second in the alphabetical list as shown in your screenshot.

Therefore the highlight indicates precedence.

I added a comment to the bug 148154 – Reordering Conditional Formats in Manage CF dialog

#7

My bad, your file looks the same.

However, you are wrong about the highlighting. I can click once on any formatting rule and it will be highlighted, regardless of what cell is selected before opening the list, so it’s not reliable. In addition, I think it always shows the rule that’s created the first. I can see in my original file that the default highlighted rule doesn’t even include the selected cell in its range.

Also, at first I thought you were right and wanted to edit my post again, but seems like I can’t edit my post twice. I apologize if later it looks like false information. (Any help is appreciated teaching me how to edit my post a second time.)

#8

The only clue is the initial view when you open the Manage Conditional Formatting dialogue. There is one condition that is highlighted and it seems that is the one that takes precedence in the list of conditions. This might be why your selected cell is not in the highlighted range.

To edit your own comment or question, just click the pencil icon at the bottom.
Cheers, Al

#9

The new user interface for conditional formatting looks better than the one in OpenOffice, but it is misleading. From the API point of view, there are no overlaps. Each cell has exactly one ConditionalFormat with multiple conditions. When you create a new C.F. overlapping the old one, the old one is simply overwritten.
The SpecialCells extension shows 3 differently formatted groups of ranges:

  1. A1:A3;C1:C3
  2. B1:B3
  3. the unformatted rest
    Bildschirmfoto von 2023-05-15 12-39-09
#10

There is no meaning in calling it the highlighting of precedence if it isn’t related to the selected cell. No matter which cell I select, I see all the formatting rules, and only the first created rule for the entire sheet is highlighted when I open the list. That doesn’t even make sense if no ranges overlap.

Even if the highlight is reworked to show which rule takes priority for a selected cell, it would still mean I couldn’t see the precedence order for the rest of the list in case the cell doesn’t activate the first applicable rule.

There is no pencil icon at the bottom:

kkkkk
kkkkk857×450 32.1 KB

Cheers, nova

#11

• If I define a rule for A2:C2 and another for B1:B3, they will both work when applicable exclusively for B2. There is no overwriting. That also means a cell can have more than one ConditionalFormat.

• The old rule is the one that takes priority in case multiple conditions are met. So when you write a new rule, you pray for the old one not to execute. (Which makes it hard to manually adjust the priority.)

I can define a rectangular range like A1:C3, so I don’t understand what I’m looking at with that extension. It doesn’t look related to my question tbh.

#15

For the question it might be different, try the pencil icon in the top right.

#17

It only opens the edit history:

kkkkk
kkkkk1124×617 36.7 KB

The white pencil icon on top left is not clickable. Switching to other display options doesn’t change anything. Double clicking on the text doesn’t let me edit, right click doesn’t bring any option to edit.

I had the edit button the first time. It just disappeared after the first edit.

#19

No, more likely it’s some unreasonable limit imposed to new users on the site. Like “no more than N edits; no more than X posts; no more than M links and L attachments per day…”

1 Like
#12

Forget “SpecialCells”. It does not really show what I wanted to demonstrate.

The API property “ConditionalFormatting” for B1:B3 has only one c.f. entry. There is no overlapping second entry for that range because any cell range has only one c.f. with multiple conditions but not multiple c.fs.

The GUI should list the first c.f. entry as A1:A3;C1:C3 rather than A1:C3 because that is the multiple selection where the c.f. takes place. B1:B3 has a different c.f. unrelated to the first one.

#13

That is not true.

You can try it yourself and when the rule for B1:B3 doesn’t get triggered, the rule for A1:C3 will work. So it is not ignored, just has lower priority.

You can even set 2 or more rules for exactly the same range, and see that they all work when their conditions are exclusively triggered.

#14

Hello @supernova01, I’ve suffered a lot with Conditional Formatting.
I came to the conclusion that the cell will be formatted by the first rule that is positive.

#16

Hello @schiavinatto, I am aware of the truth of your conclusion. We have talked about it above already.

My question is asking for a way to take control of the priority, not how it works. Because simply knowing that the oldest rule takes priority doesn’t mean much.

Thanks for the input though.

#18

… is off-topic here. No matter if you consider it a feature request, or a bug report.

#20

I wasn’t asking for a new feature, nor reporting a problem.

I was just asking if there was an existing way that I couldn’t figure out. It was clear in the post. So, it’s not off-topic.

Judging by the crowd, I guess I got my answer:

Is there any way for me to reorder them without deleting and adding again?

No.

#21

I found that the conditional formats are ordered by the ranges. I was able to change the order by appending “a1,” to the range on my later conditional format. This moved it to the top of the list.

#22

You can change the order by prepending A1; but it doesn’t change the priority of conditional formatting. That comes down to the order of creation of conditions which is not shown. The only clue is that the condition that is highlighted when you open Manage Conditional Formats dialogue is the first one created.

#23

I’m cross-posting here as this thread seems more current and in case it helps others.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.