Conditional formatting splits when inserting new rows

[physicsgal]

I have a timeline I use to keep track of my projects that has A LOT of conditional formatting to help keep me on track. Everytime I insert a row, no matter how I do it, it splits the conditional formatting. For example, if I am applying conditional formatting to this range $A$5:$BH$26 and then I insert a row, I end up with $A$5:$BH$5,$A$12:$BH$26 (if I have inserted row 11).

Does anyone know how to fix this? I have tried named ranges and relative references. I've done some looking around online, but no one seems to have a solution, or have a solution that works. I am using Excel 2010.

Thanks!

[TMS]

I don't know a way to fix it, and it can become a huge problem over time.

However, you could clear and re-apply all the conditional formatting when the workbook is opened. At least then you get a clean CF for the start of the session and it won't build up.


Regards, TMS

[physicsgal]

Thanks TMS -- what would be the best way to go about doing this? Doing it all "by hand" is out of the question unfortunately as there are probably at least 50 rules. Do you know of some code that would help achieve this and I could customize it for my needs?

[popipipo]

Copy a row
Insert this row
Now you have copied the CF in the new row.

Adjust the values in this row

[FDibbins]

@ popipop just a word of advice - be careful when copying CF in 2007 (not sure if the fixed it in later versions), it often doubles - and triples-up on the rules you have created

[TMS]

See this link ...

Re: Fix conditional formatting to never change


Regards, TMS

[popipipo]

@FDibbins

Thanks for advice

In excel 2007 you can easy adjust the range for CF.

[TMS]

My experience was that copying and deleting rows with CF in 2007 split the CF ranges.

In the specific example, the CF became unusable and the CF Rules Manager eventually could not cope. I suspect the OP is experiencing something similar.


Regards, TMS

[FDibbins]

That was my take as well, Trevor

I was just pointing out to pop that sometimes 2007 really messes up CF when you copy it (copy the CF cell, that is)

[TMS]

I was just pointing out to pop that sometimes 2007 really messes up CF

Big style

[handerson0603]

Physicsgal, my approach has been to apply CF to an entire column and ALWAYS Paste Special > Values when copying in new data. (This became a little easier with 2010, since they added a Paste Values option to the context menu when you right-click.) I have found that if I paste values to an unused portion of the worksheet, then Cut entire rows, and Insert Cut Cells into a region where they can be considered in my formulas, it maintains the formatting and the formula ranges automatically adjust. I don't know if your data and CF are too complex for this approach to work, but it has been a decent workaround for my situation.

[TMS]

@physicsgal: how you doing with this?

Regards, TMS

[physicsgal]

Hey sorry-- I've just logged back in and I thought I had the settings applied that would email me with new posts!

Thanks for all the advice, and I will be working on this next week as I have had some other projects take priority.

I will post again with comments/results/questions. Thanks for following-up TMS.