How do I prevent conditional formatting getting FUBAR when I move rows to another tab

FrumpyJones

New Member
Joined
Feb 11, 2008
Messages
25
Hi Everyone,

Thanks for all the help you've given in the past, and hopefully in advance for helping me with this new one! :biggrin:

I have a spreadsheet that goes from columns A-G and down to row infinity. I have a tab called TO BE DISCUSSED and a tab called COMPLETED, and a tab called HOLD

I have some pretty simple (But many) CF setup in the TO BE DISUUSED tab. And it's been working like a charm...

UNTIL I stated moving rows (CUT/PASTE rows to be exact; in case there is a difference to someone) to the other tabs and now it's all gone crazy. When I first created the range I made it A1:G15000 (For the love of Pete if this weekly meeting ever gets to that many rows I'ma gonna defenestrate myself). When I clicked apply to the range it auto throws the $ in front the ranges and I'm fine with that (I guess), but I notice that as I move rows out to the other tabs, my range gets all crazy with sub ranges. like $A$1;$G$19;$A$23;$G$56; etc.etc...

So, finally, the ask:

How do I stop this? Is there a way for me to move the rows without monkeying up the range of the CF? Or am I destined to just keep the range a1:g15000 in notepad++ and do some pasting every few weeks to all my CF's?
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,988
It sounds like you are referring to the AppliesTo of the Conditional Formatting.

And its working as intended, if you cut a row or insert from $A$1:$G$15000 then the result will follow the individual cells.

Do you get this problem if you Copy/Paste instead of Cut/Paste?

I get the same think in some of my CF situations and just end up using the Format painter a lot and deleting CF conditions where the Applies To has become #REF.

It might go smoother if you converted your range to a Table.
 
Solution

FrumpyJones

New Member
Joined
Feb 11, 2008
Messages
25
It sounds like you are referring to the AppliesTo of the Conditional Formatting.

And its working as intended, if you cut a row or insert from $A$1:$G$15000 then the result will follow the individual cells.

Do you get this problem if you Copy/Paste instead of Cut/Paste?

I get the same think in some of my CF situations and just end up using the Format painter a lot and deleting CF conditions where the Applies To has become #REF.

It might go smoother if you converted your range to a Table.
Hi @mikerickson,

I do think it's because I'm cutting out the rows. But copy/paste is leaving the old data there, and if I delete the data (Rather than remove the rows) then I have a crapload of empty rows over time.... so I'm looking to avoid that.

I am intrigued about this TABLE INSTEAD OF RANGE you mention. Ima gonna go and google that right now and let you know :)
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
423
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Just click anywhere in your data and press ctrl + T to convert to a table, then every time you add new data i.e. to the end of your range the table automatically expands to cover the new range
 

FrumpyJones

New Member
Joined
Feb 11, 2008
Messages
25
@mikerickson THANK YOU!

Changing the data to a table did it. Now I can add and delete rows without any issue and my CF continues to run fine! You rock!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,568
Members
417,151
Latest member
ChickenTenderer

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