Calc - conditional format gets messed up after sorting or rearranging rows

[Karl-MCS]

Good afternoon,

I have a template in Calc that I use for equipment lists. In this, I use a conditional format over a large range to apply alternating row color styles (A2:L999). Then I have another conditional format on a single column (overlapping with the other) which is a color scale (C2:C999).

I’ve noticed that after I sort the sheet or insert/rearrange rows, one of the conditional formatting ranges (although oddly not both and I have seen either one do it) gets messed up. For example, if I take row 3 and move it down to row 6, then I go to manage conditional formatting, I now have two color scales. One is C6, and the other is C2:C5,C7:C999. This also breaks the color scale on the row I moved because it is no longer in a contiguous range of cells for formatting.

To fix it, I have to go into manage conditional formatting, delete the C6 range, and set the other back to C2:C999 to fix it. That gets old pretty fast. Is there any way I can stop it from doing that? This is version 6.4.4.2 (x64).

Update: I have updated to LibreOffice 7.1.0.3, and it exhibits the same behavior. Also interesting, if I move a row and then hit CTRL-Z to undo, the conditional formatting is still messed up for the row that I moved and unmoved.

[EarnestAl]

For alternating row colours I normally set Formula is MOD(ROW();2) and select style Good unless I have created a new style for the cell. I can’t see how this could ever have two coloured rows together

If you use Format > AutoFormat styles, I think it is possible to get two coloured or two white rows together on sort because the final row (before sorting) is special.

I can see that manually cutting a row out of a range where the values of that range are affected by the cut row is going to also cut the range.

[newbie-02]

there are plenty of options, and plenty of side effects to observe, would be easier with a sample …

[PrestonM]

I am encountering this problem as well. In the attached file, in the “crop values” sheet, I have an autofiltered table with a condition to highlight the highest value in the row. However, it doesn’t seem to work as expected, and sorting the table in different ways causes different cells to be highlighted, which shouldn’t happen.
Untitled 1.ods (32.9 KB)

[schiavinatto]

@PrestonM , I believe the formula is wrong, fix the area

B2=MAX($B$2:$F$29)

[PrestonM]

That would highlight the highest value in the table. I want to highlight the highest value in each row.
…that did prompt me to try B2=MAX($B2:$F2), though, which seems to be what I needed all along.
Thanks!

[librebird]

I’m having the same problem in 2024. I’m using Version: 24.2.2.2 (X86_64) / LibreOffice Community.

I would love a way for the conditional formatting to be locked so it doesn’t get all messed up when I cut and paste cells.

I don’t understand why someone would want these conditions to be cut and pasted. Perhaps if there is no solution someone could at least explain why the current behaviour is the way it is.