# Conditional Formating

##### New Member
Hello,

I'd like to apply the same conditional formatting if the folowing numbers are found in a column 20,47,48 or 53.

Excel only does 3 conditional formats. So is there anyway of using the "or" function in a single conditional format. i.e. = "20" or "47" or "48" or 53"

Or am I just being stupid?

Cheers

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try

=OR(A1=20,A1=47,A1=48,A1=53)

or if you have many numbers to check for...list them somewhere on the worksheet, e.g. Z1:Z10 and use

=MATCH(A1,Z1:Z10,0)

eg click on cell g5 for conditional format.. chose formula is option from drop down and enter

=IF(G5=20,TRUE,(IF(G5=47,TRUE,(IF(G5=48,TRUE,(IF(G5=53,TRUE,FALSE)))))))

set whatever format you want for if it finds a match

use format painter copy across your range you ant checked

oh yeh.. I seem to like making things more complicated than they need be !.. use houdinis !

Assuming your range starts at A2:

Select all cells in the range.
Format > Conditional Formatting
Formula is =(A2=20)+(A2=46)+(A2=48)+(A2=53)
This will evaluate to 1 (or TRUE) if any values meet the criteria
Set your format and click OK

Denis

Sydney,

Your idea seems to format all blank cells.

Barry,

I can't get yours to work does the A1 refer to the top left cell? should I only apply to top left cell and then copy formatting to the other sells?

If you select your whole range of cells then use the formula which relates to the "Active cell" - which is the first cell you select when selecting your range, usually the top left.

so if your range is C4:H10

select that range with C4 active and use

=OR(C4=20,C4=47,C4=48,C4=53)

Excellent.

Thanks.
Probelm was that I didn't realsie I could select "formula is" on the conditional formating toolbar.

Cheers

Ah yes, sorry, I'd just assumed you were using that already.....

Hi,
I have a problem simialr to this however my list down goes from 0000 to 9999 and I want ever cell containing a number ##00 to be bold. I've tried various thing but the conditional format doesn't seem to recognise the ## as the normal format does.

Can anyone help on this please? If I wanted it red I would go [red]##00, instead of read I want bold.

Thanks

Replies
2
Views
312
Replies
1
Views
250
Replies
7
Views
427
Replies
3
Views
417
Replies
12
Views
500

1,217,259
Messages
6,135,508
Members
449,945
Latest member
noone12344444444

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

### Which adblocker are you using?

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

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