Conditional formatting - coloring cells across array if value exists in a list

I haven't been able to find an answer yet to this issue, maybe someone can help:

I have an array of cells, let's call it A1:D5 for simplification. In each of these cells are various different 4-digit numbers (codes). I also have a short list of codes in a column F1:F5 that fit into a certain category that I'm looking into. My question is this:

How do I write a formula in conditional formatting that I can apply to the entire array A1:D5 that checks if the value of EACH INDIVIDUAL CELL in that array (A1:D5) contains a value that exists in F1:F5 and turns those cells blue if that condition is satisfied?

I can format each cell by itself using =Countif(F1:F5,A1)>0 for A1, but I want to apply this over the whole array A1:D5 all at once and have the condition apply to each cell by itself but not the whole array.

Thanks!

The cells only contain one value each, but this formula you provided doesn't work.

Originally Posted by Norie

Use MATCH.

Select A1:D5, goto conditional formatting and enter this formula,

Formula:

Please Login or Register  to view this content.

and format as required.

No you've misunderstood, I want each individual cell to be highlighted. Using the sample book you selected, I'd want every cell in A1:D5 that contains either a 2, 3, 5, 7, or 8 to be highlighted, nothing about any particular row.

Originally Posted by ChemistB

Norie,
I think mudder wants the row highlighted if only if all values in the row are in F1:F5. I believe your formula highlights individual cells if they are in F1:F5. It would make it fairly easy to tell at a glance that all cells in a row are highlighted and thus in the list so might be good enough if mudder doesn't want to go with the dummy column.

Yes I'm not saying that this formula doesn't work, but it doesn't do what I'm looking for. I can conditionally format A1 alone easily in several different ways -- I want to format EVERY CELL in A1:D5 using this same condition all at once.

Originally Posted by Moo the Dog

Just tried Norie's corrected CF formula.

=MATCH(A1,$F$1:$F$5,0)

It works perfectly.

- Moo

My bad! It works, thank you!

Originally Posted by Norie

Did you select A1:D5 and use the formula I suggested?