Range for conditional formatting keeps changing?

[zf]

The range for conditional formatting keeps changing and I’m not sure why. I
have a very simple rule like A3:AMJ1048576 but over time it ends up being
something like
A36:AMJ39,A3:AMJ34,A35:D35,F35:AMJ35,A40:C41,F40:AMJ41,A42:AMJ1048576. I
assume it has to do with absolute/relative cell references but I’ve tried
locking some cells and it seems to have no effect. Basically, I want rules to
apply to all cells in the cell range permanently, regardless of the data.

I’m also wondering if it has to do with copying/pasting/deleting of cells,
since I’ve learned that apparently, the best way to swap the data on row A and
row B while preserving formatting (like centering text for an entire column) is
to copy row A to an empty row C, copy row B to row A, copy row C to B, and
delete row C (would love to know a better way to shift cells around without
messing up formatting. Cutting instead of copying causes the cell it was cut
from to lose is formatting).

[LeroyG]

zf,

May be you could add $ symbol into your reference, like $A$3:$AMJ$1048576.

More help in Addresses and References, Absolute and Relative.

Share results with add comment (not with Add Answer).

[mikekaganski]

I’m also wondering if it has to do with copying/pasting/deleting of cells

Of course. But you didn’t tell your LO version; the range fragmentation was addressed somewhat in recent versions. E.g., tdf#95295.

[zf]

I am on version 6.4.4.

[trav1]

also having this issue and it drives me nuuuuts. There ought to be a checkbox that enforces conditional formatting regardless of copy/paste/delete

[newbie-02]

normally conditional format for ranges should be kept (not fragmented) while working (copying, moving etc.) inside! a homogenous area since LO 6.1.0,

but it’s not ‘forced’ on ‘imports’, that would break the integrity of the imports,

thus fragmentation may occur, your sample could be a result of copying cells from other places (e.g. A1:AMJ2) to E35 and D40:E41,

for your swap-around-copies (i have to use the same circle to keep / apply correct formula references, and yes, i agree that a more elegant solution would be progress) you should likely use a buffer inside the formatted area, i did a short test with 7.0 and it holds for a copy inside - outside - back inside, but i’m not sure if that will work for other or more complicated cases,

besides: ‘row A’ and ‘row B’ are unusual in calc, letters are used for columns mostly,