Sticky Conditional Formatting Formula

gnulab

New Member
Joined
May 7, 2011
Messages
14
Hi,

How can I have a sticky formula in conditional formatting across edits?

I am attaching a screenshot of it.

Initially, the formula in conditional formatting is number 1.
Excel Formula:
=$F1="H", then shade green column $B:$B

After I had added, edited, deleted rows, the formula becomes number 2 and number 3.

My family members being the user type, did not check the results, assuming it is still $B:$B, and thus some cells are not shaded.

Any idea how can I achieve this?

Thank.
 

Attachments

  • 2021-04-03_115834.png
    2021-04-03_115834.png
    13.2 KB · Views: 11

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
A straight "copy and paste" or "insert copied cells" of cells that have CF always gets messed up as you describe.

This is a work around.
If you want to paste cells WITHIN your existing range, FIRST just INSERT new cells in your range where you want them, THEN select cells that you want to copy and when you come to paste them into the new inserted cells use the "Paste Values" or "Paste Formula" option depending on what’s in the cells to be copied, if pasting at the END of your range still use "paste values" or "paste formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,779
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top