Conditional formatting based on a 1-column named range

[changeling jack]

Hello all,

Having some trouble with Excel 2013/Windows 7. I am trying to get a column in a table to conditionally format the contents of a cell if the text is contained on one of several named ranges on a separate worksheet. Each named range has a selection of metal alloys and we use certain colors to easily identify an alloy by base element. Some things which may have bearing:

• The column I want formatted has a data validation drop-down menu sourced from a named range [Alloys] on another worksheet. It is not the same named range (e.g., [Alloys_Blue]) as those I want to use for the conditional formatting, but it contains a selection of the same entries.
• Each named range is part of a named table (e.g., [tbl_Alloys_Blue]).
• No entry appears on more than one of the named ranges which will be used for conditional formatting.

The only questions I've found on the internet about similar problems do not fix this issue as they are all specific to some other modifier being needed to make the rule work. VLOOKUP doesn't seem to do the trick.

So far I have only succeeded in coloring all cells one color regardless of their contents, or making a rule which works for the first two cells and then breaks. Does anyone have any suggestions on how to make this work?

Thanks much,
Jacob

 

[etaf]

you may be able to use a countif()

are we looking across a lot of different name range

can you give some examples of the column range and where you are looking up
and what decides the colour

perhaps even an example of your data and also a manual mock up of the expected results you want to achieve.maybe on a file sharing site - dropbox , onedrive etc

 

[Snakehips]

changeling jack,

I'm not able to test this right now or post a snapshot, however, try.....

Named ranges e.g. Alloys_Blue, Alloys_Red

Select the column range that you wish to format, e.g. A2:A20

Then add a CF using formula..... =COUNTIF(Alloys_Blue,$A2)>1 set format colour to blue.

Repeat for red with =COUNTIF(Alloys_Red,$A2)>1 etc etc

Hope that helps.

 

[changeling jack]

Snakehips & etaf,

Thanks for the advice. The column to be formatted was B, row 2. I got it to work with:

=COUNTIF(Alloys_Blue,$B2)=1 (I started with >1 as suggested but it didn't do anything)

There are a total of seven color values, which all appear to be properly implementing their own colors on the same column. They all follow the same formula above, but with the color name swapped out.

Thanks again!
Jacob

 

[Snakehips]

You are welcome. Glad we could help, despite stupid typo on my part. (Should have been >0 )