Conditional Format Based on a Defined Range

Originally Posted by Cutter

2010 can ref another sheet for CF

True, and also if a named range is being used (as in this case) then you can refer to a range on another sheet, even in Excel 2003 or 2007, indeed that's the standard method suggested to get round the problem - use a named range.

Note: as MATCH returns either a number or #N/A you can shorten the CF formula by using ISNA rather than NOT(ISNUMBER, i.e.

=ISNA(MATCH(F6,PrimaryVendor,0))

...or even use COUNTIF....

=COUNTIF(PrimaryVendor,F6)=0

Originally Posted by daddylonglegs

True, and also if a named range is being used (as in this case) then you can refer to a range on another sheet, even in Excel 2003 or 2007, indeed that's the standard method suggested to get round the problem - use a named range.

Note: as MATCH returns either a number or #N/A you can shorten the CF formula by using ISNA rather than NOT(ISNUMBER, i.e.

=ISNA(MATCH(F6,PrimaryVendor,0))

...or even use COUNTIF....

=COUNTIF(PrimaryVendor,F6)=0

Cool. Thanks for the tips.