Conditional formatting based on more than one value in a column

**Dean Simpson** · 10-29-2010, 12:02 AM

In a cell i have set up data validation so all entries are from a set list.

what i would then like is, based on a value to format the cell using conditional formatting, however when asking the error message comes up "You may not use unions. intersections, or array constants for conditional formatting criteria"

what i want is to colour a cell lets call it B1 from the following list $C$3:$C$26
the list has 24 values but i only want to format the cell if it matches 5 of the values.

any help would be appreciated

**6StringJazzer** · 10-29-2010, 12:20 AM

With this type of question that is very specific to your workbook, it is always most helpful to attach the file.

This is what is puzzling me:

the list has 24 values but i only want to format the cell if it matches 5 of the values.

This implies that the list of 24 values has many repetitions, 5 or more of any given value.

If I am interpreting this correctly, you want your conditional formatting to use this condition:

=COUNTIF($C$3:$C$26,B1)>=5

**Dean Simpson** · 10-29-2010, 12:41 AM

I have tried to attach the file but security at my employers have blocked upload, so i have cut and paste.

What i need is to be able to ammend D1 so that if it has a text value of "A1" or "F1" or "F2" or "E1" or "E2" then it will format into the colour i require.

these five values are taken from $C$1:$C$22

A B C D
1 Abbott, Jacqueline A1 A1
2 Bailey, Craig ACRO E1
3 Broom, Jason ARL F1
4 Brown, Chris B1 F2
5 Buktenica, Ivan BAGS E2
6 Bullock, Rebbekah C/L
7 Carson, Alana CRO
8 Combs, Andrew DIL
9 Daniels, Gina E1
10 Davis, Geoff E2
11 Dedear, Bill E3
12 Falvey, Matt E4
13 Gibson, Bradley E5
14 Harrison, Blake E6
15 Hatton, Richard EFL
16 Heeney, Declan EVT
17 Hort, Tim F1
18 Knox, Jayne F2
19 Le Cerf, Geoffrey H1
20 Lynn, Jason H2
21 Mariess, Flordeliza H3
22 McKeown, Paul H4

**6StringJazzer** · 10-29-2010, 12:52 AM

Originally Posted by Dean Simpson

What i need is to be able to ammend D1 so that if it has a text value of "A1" or "F1" or "F2" or "E1" or "E2" then it will format into the colour i require.

these five values are taken from $C$1:$C$22

But C1:C22 has lots of different values that aren't in your list of 5 desired values. How do you tell which ones are important?

If it's just those 5 and always those 5, then you need to create another list elsewhere in the worksheet (or on a new worksheet) that lists just those 5. I suggest you create a name for that new list, let's call it Big5.

Then your conditional formatting formula looks like:

=COUNTIF(Big5,D1)>0