Can I "protect" the "applies-to" field in cond. format rules?

I have looked long and hard for an answer to this question, so I've finally gotten to the point of asking for help!

Here's what I got:

I have a spreadsheet (Excel 2007) that uses conditional formatting in many different ways, and one of them is to underline the rows between the point where the values in column 'A' changes (project name). This helps make a very large grid much easier to follow. It's a very simple formula

Please Login or Register  to view this content.

.

I even have a macro now to take care of inserting the new rows and copying the formulas in the other parts of the spreadsheet, whenever dummies need to use it. However, each time a new row is inserted (either manually or with the macro), it splits the "applies to" field in the cond.format rule for the underlining. All of the other cond.format rules are not affected. I cannot figure out why.

And here are my questions:

• Can the "applies to" field in the cond.format rule be "protected" somehow, without actually protecting the sheet (which interferes with adding new rows correctly)?
• Is there an expedient way to reset the "applies to" field back to what it was before the insert event?
• If it makes sense to have the macro reset the "applies to" field (assuming this is possible), can anybody point me in the right direction (I'm handy with a macro, just haven't ever tried it with cond.formatting)?
• Is there something I'm maybe doing wrong in this particular rule that is causing this (the other rules don't seem to be affected)?

Thanks in advance for any good advice!

Nate

When you insert a row, Excel keeps all the references up to date. That is, suppose you have a formula in A5 that refers to A6. If you insert a row between those two rows, Excel will update the formula in A5 to refer to A7, which is where the data from A6 is now located. The same thing happens in conditional formatting formulas.

Here is a formula for conditional formatting that is immune to this effect. First, we simplify your original formula to read

=$A8<>$A9

You don't need to use the IF to create a result, because the logical comparison itself gives the result you want. Now, instead of comparing a cell to another cell by address, we use an expression that means "the cell in the next row."

=$A8<>OFFSET($A8,1,0)