Excel has made numerous changes in its conditional formatting between 2003 and 2007. Most of the new features I think are great, though there are a lot of problems with backward compatibility.
One thing I discovered this week is a trap for the unwary.
In Excel 2003 it was easy to create a conditional format for a cell, and then copy that cell or even just the formatting for that cell to other cells, and the conditional formatting would be copied also. Of course it pays to make sure that you correctly apply absolute and relative cell references in the conditional formatting if using formulas. And using paste special or the format painter you could copy just the formatting from once cell to another - including the conditional formatting.
In Excel 2007 this works fine if you are copying to cells that don't already have conditional formatting in them., BUT if you are copying to cell with existing conditional formatting the paste actually appends the new rule(s) to the existing one(s). This can cause some very strange results to occur.
For example if you want all the cells in column A to be coloured green if they are not blank - E.g., have something in them, then add a formula rule to cell A1 as follows. This part works for Excel 2003 as well as 2007.
Excel 2007 only.
Now if you were to create a conditional format in some other cell - eg Cell C1 and then copy that cell to cell A1, it wouldn't over write the conditional format in A1 but add to it. Thus there would be two rules in cell A1.
If you wanted to overwrite the format in A1 you would first need to clear it, Select Conditional formatting and Clear Rules from Selected Cells before copying and pasting from C1.
Some other references
Eric Patterson one of the members of the Excel Program Management team has written a number of articles on the Microsoft Excel Blog maintained by David Gainer. This is useful from a technical perspective, but doesn't really help much when dealing with large multi sheet spreadsheets with complex formulas and formatting applied. I will stick by my first impression of Excel 2007, treat it as a new application and don't try and make things compatible, or if compatibility is required use only 2003 features.
Eric writes on:
Note this point:
Files containing new conditional formatting can be edited with earlier versions of Excel. Users can change cell values, sort ranges, add formatting, and do a number of other tasks without disturbing the conditional formatting. In general if the user does not make changes directly to the conditional formatting on a range, it will safely reappear as designed when the workbook is opened again in Office Excel 2007.
Excel 2007 has a lot of really nice and easily applied conditional formatting features. However my advice is don't use them unless you know you will not need to use the file in a Excel 2003 environment. We use them in internal files here at Jethro, but for all client files where they require 2003 and 2007 compatibility we are restricted to using what Excel 2003 can handle. (This advice applies to all Excel 2007 only features).
Be careful when copying cells containing conditional formats on top of other cells with conditional formats - it will not overwrite the format as it did in Excel 2003.