Ask Your Question

Range for conditional formatting keeps changing?

asked 2020-05-20 23:59:19 +0100

zf gravatar image

updated 2020-08-07 20:27:06 +0100

Alex Kemp gravatar image

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 flag offensive close merge delete



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

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

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-05-21 09:35:09 +0100 )edit

I am on version 6.4.4.

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

1 Answer

Sort by » oldest newest most voted

answered 2020-05-26 10:42:40 +0100

newbie-02 gravatar image

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,

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-20 23:59:19 +0100

Seen: 400 times

Last updated: May 26 '20