Conditional Formating: How to color not just one cell at a time, but a range?

[excelamer]

In a worksheet that's full of cells containing one and two-digit numbers, I want to find cells containing a range of specific numbers and fill those cells with color.

I've managed to do this one cell\number at a time, ie, make all cells containing the number 19 fill with color, but I'd like to speed up the process and do a whole range in one go. Like, selecting cells containing, say, 1, 35, 79, 22, 35 etc, and having them all fill with the same color.

Is it possible to do this in a simple way, ie, without a formula, macro or VBA?

Like, I've managed to get to the data entry point by holding Alt and sequentially pressing H,L,H,E, but I can only make this work one number at a time. Is there a special entry format for multiple numbers?

[protonLeah]

Your description isn't quite clear to me, but on the attached sheet A2:A6 contains numbers to find in the range on the right.
Select the entire range and use conditional format formula:

Please Login or Register  to view this content.

[Rick Rothstein]

Let's say the range you want to Conditionally format is F1:H9 (change as needed). Select F1:H9 with F1 (the upper left cell) being the active cell. Give this Conditional Format formula a try (modify the list shown in blue as needed but make sure the list starts and ends with a comma and that there are no spaces in the list)...

=AND(F1<>"",ISNUMBER(FIND(","&F1&",",",1,35,79,22,")))

Note: You had 35 listed twice so I omitted one of them.

[excelamer]

protonLeah and Rick Rothstein: Thanks for the suggestions but, sorry, I couldn't make them work. Probably too dumb with Excel.
I've attached a screen shot of protonLeah's worksheet that I hope will better illustrate what I'm looking for.
Being a newbie, I found that all I had to do was to select my data, then press\hold Alt and, in turn, press H, L, H, E.
This brings up the box in the screen shot where I've entered '8'.
My problem is that I can select and process numbers one at a time, like the '8', but I can't see any way to enter more than one at a time in the box. If I enter, say, 8, 12, 14, etc, nothing happens.
Maybe it's not possible?Attachment 662942

[Rick Rothstein]

Your attachment was invalid... read the yellow banner at the top of this forum's window. Also, as screen shot is usually not sufficient...better would be a copy of the work book so we can see exactly what you have (remove any sensitive information that may be in the workbook first, though).

[excelamer]

Invalid attachment? I can click the above Attachment 662942 link and see the screenshot. I don't understand why you can't. I'll try sending it as an Excel file.

[excelamer]

Okay, here's the whole file. It's the same one protonLeah sent, less the data in A2-B7, so that the originally highlighted cells don't confuse the results.
Select the area D1 - L18. Hold down Alt on the keyboard and press, one at a time, H, L, H, E. You should now see a data entry box. Type in any number from the table and its cell will be colored. If there are more than one of the selected number, all of the cells that contain the number will be colored.

My question: Is it possible to perform this process, but enter a group of numbers instead of one? Ex: like these six from column D - 77, 43, 63, 30, 72, 33. Otherwise, it means that the process needs to be repeated 6 times.
Or use a formula, of course, if it's the only way to do it.
Personally, I'd rather do it my way, but it might not be possible to enter multiple numbers.

Ooops, forgot to say I'd already selected '3' using my method.

[protonLeah]

Your method will not work; that method expects/requires a single value or cell ref. With my sheet, all you have to do is put the desired numbers in column A. You can/should clear column B. I only used that to double check. MY conditional format formula only looks at A2 to A6 so you can enter up to 5 number, or modify the formula to suit.
By the way where are the lookup numbers coming from?
Also, what does "... data entry point..." mean?

[excelamer]

...By the way where are the lookup numbers coming from?
Also, what does "... data entry point..." mean?

If by 'lookup numbers' you meant '77, 43, 63, 30, 72, 33', they're in the D column of your table. I just used them as an example of multiple numbers.
And the data entry point I referred to is the, um, 'box for putting numbers in' that appears when you hold down Alt on the keyboard and press, one at a time, H, L, H, E.
Is that box not a data entry point?

I'll try your formula again, but I couldn't make it work when I first tried it.