I have a spreadsheet that I want to have conditional formating on a cell,
based on the contents of this cell and another cell. For example, I have a
cell, S3, that contains this conditional format:
Formula is =AND(S3="",$AL3="") - - and then color it red if true. If I use
the format painter to copy the format to cell S4 it works fine. In fact, it
works fine when copying cell-by-cell-by-cell; the formula updates to reflect
the new row as you would want. However, if I copy the format to a selected
range such as S4 to S10, all the cells in the range will reference row 4, not
their own row. If I use paste special/format, it has the same behavior.

When I do the same operation in Excel 2003, it does what I want, namely I
can paste the format to the selected range and each row updates to reflect
itself.

Is this a bug or a feature? If either, is their a workaround other than
copying the spreadsheet from EX2007 to EX2003, making the updates, and then
copying it back?

Thanks