Conditional Formatting bug?

[janpublic]

Hi,

I've discovered a bug in the conditional formatting in Mac Office 2004
with all the latest service packs. I've tested on a Windows version of
Office and it doesn't have this bug.

I've made a demo spreadsheet available at
http://exceldemo.theboard.co.za/Excel_format_bug.xls

You'll immediately see the difference if you open the Mac and Windows
versions.

The basic idea is this: I want a drop-down list and be able to select
an item. However, I want to be warned when I've selected the same item
twice or more in a row, by formatting it in red. Column B works as
expected, however column C to H doesn't. The problem seems to be with
the $ sign. If, for example, I change the condition in cell G1 from
=HLOOKUP(G1, $A1:F1, 1,FALSE) to =HLOOKUP(G1, A1:F1, 1,FALSE) it works
as expected. However, if I take the $ out, I can't write the
conditional formatting in cell B1 and "Paste Special" the formatting to
a large matrix of other cells and still have it work correctly.

Any help or ideas?
Thanks.

 

[JE McGimpsey]

I've discovered a bug in the conditional formatting in Mac Office 2004
with all the latest service packs. I've tested on a Windows version of
Office and it doesn't have this bug.

Yep, it's a bug.

This works...

CF1: Formula is =COUNTIF(OFFSET($A1,0,0,1,COLUMN()),B1)>1

 

[janpublic]

Thanks for the answer. Do you know how it can be reported to Microsoft?

Jan

 

[JE McGimpsey]

Thanks for the answer. Do you know how it can be reported to Microsoft?

Choose Feedback on XL from XL's Help menu...