Mar 07 2018 12:28 PM
Mar 07 2018 12:28 PM
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.
Mar 07 2018 03:02 PM
Mar 07 2018 03:19 PM
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.
Mar 07 2018 03:26 PM
Mar 07 2018 03:47 PM
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
Mar 07 2018 04:09 PM
Mar 07 2018 04:12 PM
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.
Mar 07 2018 04:24 PM
Apr 28 2021 01:16 AM
Dec 01 2021 10:43 AM
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.
Sep 26 2023 03:18 PM - edited Sep 26 2023 03:23 PM
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:
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.