Conditionl format for 1 cellthat looks at 3 cells.


M

Mark

Thank you experts for all your help:

Question:

Conditionl format formula for "N7"

1. If C7,D7,E7 are >28 turn red

2. If C7,D7,E7 are <28 turn green

I can do the color part(easy I know)

Queston:

Is there a way around the 3 conditional format limit for a cell?

Thank you in advance.

Mark
 
Ad

Advertisements

S

Stefi

Your question is ambiguous. If you mean that all of C7,D7,E7 should be >28 to
turn the cell red then

=AND(C7>28,D7>28,E7>28)

the same for green with < instead of >. If this is not the case, specify it!


Regards,
Stefi

„Mark†ezt írta:
 
P

Pete_UK

With N7 selected, click on Format | Conditional Formatting and choose
Formula Is rather than Cell Vlaue Is in the first box. Enter this
formula:

=AND(C7>28,D7>28,E7>28)

Then click the Format button, choose the Patterns tab and choose red.
Click OK, then Add to set up your second condition. Formula Is again,
with this formula:

=AND(C7<28,D7<28,E7<28)

Set this for green, then OK twice to exit the dialogue boxes. The
formulae will set the colours if all 3 conditions are met.

XL2007 has more conditional formats per cell (I think it's about 60),
and Bob Phillips has a free add-in here:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

which will give you up to 30.

Hope this helps.

Pete
 
J

John

Hi Mark
If you're using XL2003 and older, the answer is NO except a macro will do
it.
HTH
John
 
M

milo

Try this:

1) Conditional Format for Cell (N7)

Conditional1: Red
=IF(OR($C$7>28,$D$7>28,$E$7>28),TRUE,FALSE)

Condition2: Green
=IF(OR($C$7<28,$D$7<28,$E$7<28),TRUE,FALSE)


2) Enter a formula to Cell (N7)
=MAX(C7:E7)


3) Now you try enter any number in cells C7,D7,E7.

Regards.
 
S

Stefi

Yes, it's exact now! The formulae are

=SUM(C7:E7)>28
and
=SUM(C7:E7)<28

Regards,
Stefi

„Mark†ezt írta:
 
Ad

Advertisements

M

Mark

Stefi,

I'm not smart enough to be ambiguous!

Barring that.

Yes!

C7 + D7+ E7 if > than 28 cell N7 colors.

each cell has a number.

Is what you wrote still the same formula. Thank you for helping.

Mark.
 
M

Mark

Thank you Pete. osh, the help hee is fanatstic.

Pete_UK said:
With N7 selected, click on Format | Conditional Formatting and choose
Formula Is rather than Cell Vlaue Is in the first box. Enter this
formula:

=AND(C7>28,D7>28,E7>28)

Then click the Format button, choose the Patterns tab and choose red.
Click OK, then Add to set up your second condition. Formula Is again,
with this formula:

=AND(C7<28,D7<28,E7<28)

Set this for green, then OK twice to exit the dialogue boxes. The
formulae will set the colours if all 3 conditions are met.

XL2007 has more conditional formats per cell (I think it's about 60),
and Bob Phillips has a free add-in here:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

which will give you up to 30.

Hope this helps.

Pete
 
Ad

Advertisements

M

Mark

Perfect. Thank you very much

Stefi said:
Yes, it's exact now! The formulae are

=SUM(C7:E7)>28
and
=SUM(C7:E7)<28

Regards,
Stefi

„Mark†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top