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