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

[FrumpyJones]

Hi Everyone,

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

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?

 

[mikerickson]

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.

 

[FrumpyJones]

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]

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]

@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!