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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

BruceyBonu$

Board Regular
Joined
Mar 6, 2006
Messages
74
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
 

BruceyBonu$

Board Regular
Joined
Mar 6, 2006
Messages
74
oh yeh.. I seem to like making things more complicated than they need be !.. use houdinis !
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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
 

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
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?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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)
 

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
Excellent.

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

Cheers
 

zx6roo

New Member
Joined
Feb 17, 2006
Messages
15
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 :)
 

Forum statistics

Threads
1,136,430
Messages
5,675,803
Members
419,586
Latest member
RoteichA

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
Top