Excel compatibility problem (2003 v 2007)?

At our office we are still on Excel 2003.  At home I use Excel 2007.

I sometimes take work home (yeah, sad git).  One type of work that I commonly take home is Excel workbooks that I have under development.  Once home, I open them in Excel 2007 in "compatibility mode", do a bit of development, save them and transport them back to the office for further development or implementation.  This may go through several iterations.

The operation of one fo these workbooks involves the copying of a template row in a worksheet (row 7) and pasting it into several rows below, generally more than once.  So, I might (say) copy row 7 down to rows 12-20, and later to rows 21-25.

Some of the cells in row 7 contain conditional formatting.  For example, Cell L7 contains the following criteria:

Formula Is: =AND($H7<>"Suspense",$H7<>"Allocation",$L7<>0)
That is all you get in Excel 2003.  In Excel 2007 you get in addition to each rule a field "refers to", which is populated by the reference to cell $L$7 in this case.

All works fine in Excel 2003.  And indeed if I only copy the template down ONCE in Excel 2007 it also seems to work fine (say from row 7 to rows 12-20).  But then if I repeat the process (only in Excel 2007) to rows 21 to 25 then on attempting to save the workbook I am prompted with a warning that there are overlapping conditional formulae that will give rise to a loss of functionality in earlier versions of Excel.

On examining the conditional formulae I observe that cell L21 now shows 2 criteria rules:

Formula: =AND($H20<>"Suspense",$H20<>"Allocation",$L20<>0)
refers to: $L$20:$L$24

Formula: =AND($H12<>"Suspense",$H12<>"Allocation",$L12<>0)
refers to: $L$12:$L$24
It should show just the one rule:

Formula: =AND($H21<>"Suspense",$H21<>"Allocation",$L21<>0)
refers to: $L$21 (or $L21 or L21, I am not bothered which)
 

Any ideas?

With kind regards

Clint Westwood

Comments

Mr Google suggests ....

mikewhit | | Permalink

look here http://www.spyjournal.biz/excel/conditionalformat_excel2007 where it appears "that's the way it works"

:-(

nogammonsinanundoubledgame's picture

Ugh. Thanks for that.

nogammonsinanun... | | Permalink

Presumably Excel 2010 will be much like 2007 in this regard.

Oh well, I was using a macro to do the copying and pasting, so I suppose I can set the conditional formats within the macro.  "Just" need to make sure that the macro behaves the same way in both versions.  Any traps of the unwary, please just post below, thanks :)

With