Conditional Formating

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
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)
 
Upvote 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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
Excellent.

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

Cheers
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Back
Top