# Range for conditional formatting keeps changing?

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

edit retag close merge delete

zf,

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

More help in Addresses and References, Absolute and Relative.

( 2020-05-21 00:10:01 +0100 )edit

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

( 2020-05-21 09:35:09 +0100 )edit

I am on version 6.4.4.

( 2020-05-22 23:03:37 +0100 )edit

Sort by » oldest newest most voted

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,

more