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