Forum Discussion
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.
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
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
- 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
DamienThank 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.