Conditional Formating

[ToadOnRoll]

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

 

[barry houdini]

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$]

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$]

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

 

[SydneyGeek]

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]

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]

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]

Excellent.

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

Cheers

 

[barry houdini]

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

 

[zx6roo]

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