Keeping Conditional Formatting when moving cells

[Nguyen Giang]

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.

[#2]

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
Damien

[#3]

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

[#4]

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

[#5]

Dear Damien,

The reason i don't use absolute cell referencing is due to my other worksheet constantly being updated based on market condition. So the reference is sometime change and i need to auto-filled them to make the changes on my worksheet alot faster.

Please see attach Excel for reference

[#6]

Hi Nguyen

I can't think of an easy way to do this (some of the other more experienced folks may know a better answer).

They only way I can think of is:

1. Drag and drop the cell/s you want to move (retains formula).
2. Clear the formatting from the cells just moved (Home tab under Editing, Clear > Clear Formats). This will remove the conditional formatting from the newly pasted cell/s.
3. Autofill the original cell/s with a cell from that has conditional formatting.
4. Click onto a formatted cell (e.g. U9), click Format Painter, and click onto your newly pasted cell/s to restore the format.

Only takes a few seconds to do and this is a workaround.

If there's a better solution out there then I'm happy to learn it too!

Let me know how you go on this please?

Good luck
Damien

[#7]

Thank you very much for your help Damien,

I'm trying to find a way so that for the conditional formatting when a cells is moved the "Applied to" field of the formatting could be locked or somehow to stop that field from changing. Then it should solve the problem. But so far i can't find any way to stop that field from changing when a cells is moved.

[#8]

Hello friend

Sorry I can't be of more help. I have done a Google search and there are other forums raging on about the same issue but I haven't seen as yet a solid answer.

Maybe someone else on the forum has a creative answer that will make it easy for you to do what you are requesting?

Good luck and best wishes, Nguyen!

Cheers
Damien

[#9]

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)

[#10]

@Nguyen Giang 

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.  

[#11]

@Nguyen Giang 

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.