Conditional formatting reference behavior changed after upgrade

Hi, I'm having some issues with a worksheet on which the behavior changed after a recent upgrade from Excel 2003 to Excel 2010, and after spending about 3 hours trying to make it work I'm coming to you guys in the hopes of help.

The spreadsheet keeps track of the contents of a file directory, and consists of two columns. The first column is automatically populated by the import of a text file directory listing. The second column is effectively a copy of the first, with conditional formatting to flag cells that differ from the first column. If a file is added to or removed from the directory, then the "C" column will differ from the "A" column from that point down, and it will be flagged appropriately.

My problem is this: In Excel 2003, if a file was removed, I could simply delete that cell from column "C," allowing the cells below to shift up, and everything would match again, and the flagged formatting would go away. The references in the conditional formatting were solid, in that C35 would always try to match to A35, no matter how many lines were added or deleted. That behavior has changed in Excel 2010 such that the references are not moving with the cells. Thus if you deleted cells C35, 36, 37 and allowed the ones below to shift up, the conditional formatting is changing such that C35 (formerly C38 before the deletion) is trying to match to A38, C36 to A39, etc...

I've had no luck getting it work the way it used to, despite trying every combination of relative and absolute cell references I can think of. A page from the spreadsheet in question is attached. In it you will see that the lines from 35 on do not match, due to the deletion of three files. Previously, I would delete C35:C37, move the remaining cells up, the new C35 (current C38) would match A35 and so on, and the red would go away. Now when they move up, the red does not go away, because the conditional formatting is adjusting itself as I detailed above.

The workbook attached is in 2003 mode, but if it needs to be resaved to 2010 mode to effect a correction that is perfectly acceptable. Any advice you can give would be greatly appreciated.

Book2.xls

Edit: This post was in response to FDibbins; npamcpp hadn't posted when I hit reply. I will try npamcpp's suggestion now. Thanks.

I think I have tried that already, but just to make sure, I tried it again without success. I started from the document uploaded here, and tried "use formula" with both "=a1<>c1" and "=$a1<>$c1", because I'm not sure which is the "correct" syntax for this application. In both cases the formatting applied correctly initially, but when I deleted the three bad lines, the formatting stayed. I was left with a new rule, with the original rule only applying to lines 1-34, and the new rule applying to lines 34-65536. (Top half of image below)

I did find that if I go in to "Manage Rules," adjust the "Applies to" field back to "=$C:$C" and delete the newly created rule, as in the bottom half of the image below, then everything goes back to the way it's supposed to be. I'm not strong enough on macros to automate this action, sadly, and I can't help thinking that there's something I'm missing here that would make it "just work like it used to."

excel.jpg

Originally Posted by npamcpp

First, I cannot reproduce the behaviour you describe for Excel 2003. If I delete C35 to C37, the conditional formatting in the new row 35 will refer to row 38, not 35. That is the same in Excel 2010.
What you want to achieve can be done with a conditional format that uses a formula instead of the drop-down pre-defined choices. Select cell C1, then click Conditional Formatting > Manage Rules > Edit the rule > select "Use a formula to determine ..." and enter this formula

=C1<>INDEX($A:$A,ROW())

Hit OK and test. If you write this formula manually, please be aware that C1 is a relative reference and you need to be in cell C1 when you enter the rule.

This worked exactly like I need it to, thank you so very much. I have a "just enough" knowledge of Excel, and clearly this was beyond me and my Google-fu.

On a related note, you couldn't reproduce the behavior in Excel 2003 because I was actually running Excel 2000, where it worked exactly as I described. Mea culpa.

Thank you again, I've starred your post and will be marking this solved.