View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Hamish Hamish is offline
external usenet poster
 
Posts: 14
Default Conditional Formatting: Logical AND fails to work with INDIRECT(..

The formula I wish to use for conditional formatting is:
=AND((MOD(COLUMN(),4)=1),(INDIRECT(ADDRESS(ROW(),C OLUMN()))=MIN($E17,$I17,$M17,$Q17,$U17)))

If I use:
=INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN($E17,$I17,$ M17,$Q17,$U17)
That works correctly. The highest value in the row is highlighted.

If I use:
=AND((MOD(COLUMN(),4)=1),(1=1))
That works correctly. The first and every 5th column is highlighted.

However, if I use:
=AND((1=1),(INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN( $E17,$I17,$M17,$Q17,$U17)))
This does not work correctly. Nothing is highlighted.