Home

Keeping Conditional Formatting when moving cells

Highlighted
Nguyen Giang
Occasional Contributor

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.

7 Replies

Re: Keeping Conditional Formatting when moving cells

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

Re: Keeping Conditional Formatting when moving cells

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.

Re: Keeping Conditional Formatting when moving cells

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

Re: Keeping Conditional Formatting when moving cells

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

Re: Keeping Conditional Formatting when moving cells

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

Re: Keeping Conditional Formatting when moving cells

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.

Re: Keeping Conditional Formatting when moving cells

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