# Conditional Formatting for Multiple Numbers

#### binar

##### Board Regular
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Joe4

##### MrExcel MVP, Junior Admin
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:
Code:
``=OR(A42=1,A42=20,A42=29,A42=40)``

Last edited:

#### etaf

##### Well-known Member
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

Last edited:

#### binar

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

Replies
2
Views
91
Replies
1
Views
110
Replies
1
Views
159
Replies
2
Views
364
Replies
1
Views
360

1,191,165
Messages
5,985,034
Members
439,935
Latest member
Monty238

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back