Inserting new rows splits conditional format

Hey everyone,

I've created a rather long macro to format a workbook the way I need it. Everything works fine in the macro itself, I just have a simple question. Part of the macro adds conditional formatting to highlight rows which contain data in a certain column. Once the macro's done however, I need to add some new rows to the sheet...every time I do this it splits up the conditional formatting into multiple rules and it stops working.

So ,my question is...

How can I make it so that, when I insert rows after the macro's done, it doesn't screw up my conditional formatting.

Just fyi: the conditional formatting rules right now are applied to =$2:$1048576 because it also doesn't work if the header is included.

Thanks for any help,
AJ

I'm not given that option when I insert new rows.

I'm sorry, I can't find what you are referring to. I usually insert by right-clicking the left side of the sheet, but I'm not seeing it there or when I go the long way on the ribbon.

I found the problem, one of my conditions included the header in its formula. Fixed it, works fine now.

Thank you!

After a lot of searching and testing, I have finally figured out how to stop Conditional Formatting "Applies To" cell formulas from changing. Create a Table of your data (select the cells, press Ctrl-T) and then name your table (e.g. tblTestData). Now when you create CF rules, choose your Table name from the drop down list at the top of the Conditional Formatting Rules Manager "Show formatting rules for:". Like Named Ranges, the table's size is dynamic; however, unlike using a Named Range for the "Show formatting rules for:", when you add and remove rows of data within the table, the CF Rules configured specifically for the table's "Applies to" formulas update automatically without the "splitting". You can limit the columns within a table that a particular formatting rule applies to by using just absolute column references.

For example, tblTestData has the cell range of $B1:$H10. If you need a rule to highlight cells only in columns B & C, create the rule under your Table Name (tblTestData) and enter $B:$C in the "Applies to" field.
You can then have another rule that only highlights cells in columns C-F by entering $C:$F in the "Applies to" field for that tblTestData's CF Rule.
Additionally, you can then have yet another rule that only highlights cells in columns B-C, F & H by entering $B:$C,$F,$H in the "Applies to" field for that tblTestData's CF Rule.