Forum Discussion
Keeping Conditional Formatting when moving cells
Dear Excel Community,
I would like some help with the following issues.
So i setup a conditional formatting where it would applies to certain cells in a row i.e. C9 to T9
it would highlight the min and max value from that range. But when i move a cell down or up a column i.e. i move F9 to F10., it mess up the conditional formatting range that it applies and changes it to =$C$9:$E$9,$G$9:$T$9,$F$10.
Is there a way so that when i move a cell around, the range in which these formatting applies too doesn't change, i.e. keeping it with C9:T9
Thank you very much for taking your time to help me with this issue.
I've struggled with this issue for a long, and there's no way to lock down the "Applies to" so that it doesn't get overwritten when you copy or move a cell inside the "Applies to" range.
Here's a creative solution I came up with:
- List the range you want to apply the conditional formatting directly in the formula.
- Example: =AND(ADDRESS(ROW(),COLUMN())="$H$2",$H$2<>"")
- The ADDRESS(ROW(),COLUMN()) will look at the current cell, to check if it matches what you want to highlight, e.g. "$H$2".
- The second part of the AND condition is where you add your formatting rule, e.g. $H$2<>"".
- Set the "Applies to" the entire worksheet:
- =$1:$1048576
This way, whenever a cell is copied or moved from anywhere in that worksheet, it will keep the same conditional formula. You could apply this to other worksheets you might have.
- List the range you want to apply the conditional formatting directly in the formula.
Think the best would be for Microsoft to allow you to 'lock range' on a conditional formatting rule.
Been reading several work-arounds that require forever maintenance. Adding the range lock would be a huge help.
It may be possible to set up a table, and then add the formatting to that table, but is extra work and not the simplest approach.
- The safest way to "move" content without messing with conditional formatting (i.e. range, to which it applies): 1) select source data and copy (Ctrl+C, no mouse dragging) 2) select target (top left cell or range of same size) 3) Rclick - paste values or paste formulas (new versions of Excel - clibboard icon with "123" or "ƒx") 4) clear source (select, press Delete)
Note: In the "paste special" submenu, there are also options "paste values with number format" ("123" with "%") and "paste formulas with number formats" ("ƒx" with "%"), these should work the same way (copies also custom number formatting but conditional formatting remains intact). You should certainly avoid those icons with brush and most others, mainly the first one (clipboard with plain paper - general paste which is equal to Ctrl+V) - Hi Nguyen
Thanks for the question.
Have you tried copying and pasting the cell you want to move, and then deleting/clearing the original cell you have just copied?
It will retain your conditional formatting and you move that value to another location.
Cheers
DamienDear Damien,
I did try to copy and paste, but the only problem was that when i copy and paste the cells the reference within the formula of those cells change also.
- Hi Nguyen
So the cell formulas are not using absolute cell referencing?
Is that something worth pursuing to lock your formula down before moving the cells around?
If you can upload the original sheet, it will give a good idea?
You can also reference this Microsoft article which gives some thought around this.
https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6
Let me know what you think?
Cheers
Damien
