Conditional Formatting for Multiple Numbers

[binar]

Fellow Forum Members,
Under Conditional Formatting -> New Rules -> Use a Formula to determine which Cells to Format -> Format Values where this Formula is True Field; I inserted the formula shown below:

=OR($A$42:$D$151={"1","20","29","40"})

Much to my disappointment I get an error message informing me it is not allowed to define a Range of cells within Conditional Formatting.

Nevertheless, my goal is to have EXCEL 2013 automatically cell highlight for me in YELLOW any time the numbers 1, 20, 29 and 40 appear within the $A$42:$D$151 range. Is there any other formula that will allow me to define a range within the Conditional Formatting new rule field? Alternatively, is there any other work around outside of Conditional Formatting that will automatically YELLOW highlight for me every appearance of the numbers 1, 20, 29 and 40 within the $A$42:$D$151 range? Any help will be greatly appreciated. Thanks in advance.

 

[Joe4]

When you write a Conditional Formatting formula, you write it for the one/current cell (and don't include the whole range you want to apply it to in your formula).
So, if you want to apply it to the whole range A42:D151, you would highlight that whole range first, then write the formula as it pertains to the first cell in your range. Excel is smart enough to adjust it fro the other cells.

Also, are you checking for numeric values or numbers formatted as text? If your numbers are numeric, you do not want double-quotes around them (that is used for Text entries).

So, the formula I would use is:

=OR(A42=1,A42=20,A42=29,A42=40)

 

[etaf]

select
the range A42 to D151
then as a formula use
=OR(A42=1,A42=20,A42=29,A42=40)
now it will change for each cell and test all the cells between A42 to D151

BUT if they are numbers then you dont want to put "" around as that is TEXT

 

[binar]

Thanks to all for your help. My Conditional Formatting formula now works great after I changed the formula as advised.