Conditional Format Based on a Defined Range

I have a defined range on another sheet within a table.
On my main sheet I have a data validation drop down list with those values populated from my defined range.

For some reason when I try to conditionally format that drop down list, I apply the rule to my range (labeled PrimaryVendor), but it only detects the first value in my range.

Here's my example:
Engineering Release.xls

Under the Primary Vendor Column is a TAHORN that is red when I believe it should be normal because it is an accepted value per my defined range (PrimaryVendor).

Thanks for any help.

****************************

Here's what worked for me:

Originally Posted by Ace_XL

I changed the formula to

Please Login or Register  to view this content.

to find exact matches

In your conditional formatting rules use this formula instead

=NOT(ISNUMBER(MATCH(F6,PrimaryVendor))) --- format text red

for what you want to achieve, you need to use "use formula" in CF. However, CF doesnt like to reference to another worksheet (at least in my 2007). perhaps if you moved your reference range to the same sheet?

Originally Posted by Ace_XL

In your conditional formatting rules use this formula instead =NOT(ISNUMBER(MATCH(F6,PrimaryVendor))) --- format text red

Here's my example using your formula: Engineering Release.xls
I'm unsure as to how those functions work (MATCH and F6 specifically), but it seems very random as to what values it affects.

Originally Posted by FDibbins

for what you want to achieve, you need to use "use formula" in CF. However, CF doesnt like to reference to another worksheet (at least in my 2007). perhaps if you moved your reference range to the same sheet?

And I think Cutter is right, I have 2010 and it doesn't look like moving the table to my active sheet makes any difference. Plus I was using the "use formula" in the Conditional Formatting with the formula: =F6<>Primary_Vendor.
My problem with that is only TACNC (which is the first item in my defined range) becomes an accepted value and everything else (such as TAHORN or TAMACH) are red.

I'm unsure as to how those functions work (MATCH and F6 specifically), but it seems very random as to what values it affects

I changed the formula to
=NOT(ISNUMBER(MATCH(F6,PrimaryVendor,0))) to find exact matches

The formula now checks for an exact 'Match' based on the input in the active cell (F6 in your first case) with your 'PrimaryVendor' list . Note the relative reference to F6, this will change to F7, F8 and so on for the next rows. If it finds a match it will return a number which will be identified as a TRUE by the ISNUMBER function, the NOT at the beginning will convert this TRUE to a FALSE and the condition will not be met, thereby not changing the format of the cell.

However, if it does not find a match, the ISNUMBER function will return a FALSE which will then be converted to a TRUE and the format will change.

It does not work on a random basis as the format will change only for values that are not found in your PrimaryVendor list.

Hope this helps! See attached.

@ Cutter thats why i said (at least in my 2007), good to see that have changed that in 2010 tho

Originally Posted by Ace_XL

I changed the formula to
=NOT(ISNUMBER(MATCH(F6,PrimaryVendor,0))) to find exact matches

As far as I've been able to 'stress test', this seems to be the solution. Thanks for the time and well-thought solution Ace!

Originally Posted by Ace_XL

Note the relative reference to F6, this will change to F7, F8 and so on for the next rows.

And like I said, I haven't found any issues, but are the references changing in the background? Because in the conditional formatting it says F6 for all of the cells.

Thanks again!

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.