Keeping Conditional Formatting when moving cells

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.

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.