Conditional formatting for multiple numbers

[Mitch21]

I want to highlight certain numbers in the list below.

Here are the numbers I care most about:
0, 15, 20, 25, 30, 40, 45, 60, 75, 80, 90

My preference would be to highlight any number not listed above in red.

Another option would be to highlight any number that IS on the list above in green.

How can I write a conditional formatting formula for this?

Thanks!

0
15
17
20
26
25
55
30
40
45
88
60
75
80
105
156
90
111
200
115

<tbody>
</tbody>

 

[Joe4]

One way:

=OR(A1=[COLOR=#333333]0,[/COLOR]A1=[COLOR=#333333]15,[/COLOR]A1=[COLOR=#333333]20,[/COLOR]A1=[COLOR=#333333]25,[/COLOR]A1=[COLOR=#333333]30,[/COLOR]A1=[COLOR=#333333]40,[/COLOR]A1=[COLOR=#333333]45,[/COLOR]A1=[COLOR=#333333]60,[/COLOR]A1=[COLOR=#333333]75,[/COLOR]A1=[COLOR=#333333]80,[/COLOR]A1=[COLOR=#333333]90)[/COLOR]

 

[Peter_SSs]

To highlight numbers not in the list, select A2:Axx and apply this Cf formula.

Excel Workbook
	A
1
2	0
3	15
4	17
5	20
6	26
7	25
8	55
9	30
10	40
11	45
12	88
13	60
14	75
15	80
16	105
17	156
18	90
19	111
20	200
21	115
CF Not in List

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =ISERROR(FIND("|"&A2&"|","|0|15|20|25|30|40|45|60|75|80|90|"))	Abc

 

[Joe4]

My method was for the "included" ones.
To use the same method for the "excluded" ones, use the same concept, i.e.

=AND(A1<>[COLOR=#333333]0,[/COLOR]A1<>[COLOR=#333333]15,[/COLOR]A1<>[COLOR=#333333]20,[/COLOR]A1<>[COLOR=#333333]25,[/COLOR]A1<>[COLOR=#333333]30,[/COLOR]A1<>[COLOR=#333333]40,[/COLOR]A1<>[COLOR=#333333]45,[/COLOR]A1<>[COLOR=#333333]60,[/COLOR]A1<>[COLOR=#333333]75,[/COLOR]A1<>[COLOR=#333333]80,[/COLOR]A1<>9[COLOR=#333333]0)[/COLOR]

Peter's method is a little shorter than mine (quite clever!).

 

[Mitch21]

To highlight numbers not in the list, select A2:Axx and apply this Cf formula.

CF Not in List

	A
1
2	0
3	15
4	17
5	20
6	26
7	25
8	55
9	30
10	40
11	45
12	88
13	60
14	75
15	80
16	105
17	156
18	90
19	111
20	200
21	115

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"></colgroup><tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format A2 1. / Formula is =ISERROR(FIND("|"&A2&"|","|0|15|20|25|30|40|45|60|75|80|90|")) Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

That didn't work for me unfortunately. I previously failed to mention that I have these numbers formatted as currency in the list. Would that make any difference?

 

[Joe4]

That didn't work for me unfortunately. I previously failed to mention that I have these numbers formatted as currency in the list. Would that make any difference?

Did you try my suggestions?

If they are numbers entered as numbers, with currency formatting, it should work (as long as there aren't any decimals that you are hiding).
If they are numbers entered as Text, it won't work, as written.

 

[Mitch21]

Did you try my suggestions?

If they are numbers entered as numbers, with currency formatting, it should work (as long as there aren't any decimals that you are hiding).
If they are numbers entered as Text, it won't work, as written.

I did try those as well. It's not working for me.

 

[Joe4]

Based on your example above, enter these three formulas anywhere on your sheet and tell us what they return:
=ISNUMBER(A3)
=LEN(A3)
=A3=15

 

[Mitch21]

based on your example above, enter these three formulas anywhere on your sheet and tell us what they return:
=isnumber(a3)
=len(a3)
=a3=15

true
2
true

 

[Joe4]

OK, those seem like valid numbers. Perhaps you are not setting up the Conditional Formatting properly.

Try it again, using these steps:
1. Select the entire range that you want to apply this to.
2. Go to Conditional Formatting, select New Rule, and select the "Use a formula to determine which cells to format" option
3. Enter the formula as it applies to the very first cell in your selection. This part is very important! The ranges used in your formula must correspond to the first cell in your selection. Excel is smart enough to adjust it for the rest. So, if you select cells A2:A100, then your formula should reference cell A2.
4. Choose your Formatting option (color cell or text).
5. Click OK.

I see people mess up step 3 quite often. They will select the entire column A, but then write the formula referencing A2 instead of A1, because A1 contains headers.
The formula MUST correspond to the first cell in your selection.