Multiple Conditional Formatting in Excel

[privxls]

Hi Mr Excel,

I would like to ask a new favor from all of you who may have encountered this:

It goes like this:
-> =IF(D4>=D3) background is yellow
-> =IF(D4<D3) background is red
-> =IF(AND(D5>=D3,AND(D5>=D4) background is yellow
-> =IF(AND(D5>=D3,AND(D5<D4) background is blue
And so on... however, not all the conditons are being followed, how many conditions do I need to set? Or is this even possible?

Kind regards,
Priv

 

[Peter_SSs]

Clarification please.
For D6 to be yellow, does it need to be >= all 3 above or just the 2 immediately above?
For D6 to be blue/purple/red, does it need to be compared with (D3 and D5) or (D4 and D5) or something else?

 

[FDibbins]

You will need a rule for each color and condition. Set up the sequence so that the top-level criteria are met 1st, and make sure to check the "stop if true" box

 

[privxls]

Hi Peter,
Each cell just needs to be compared to the one above it -- with the exception of D3 as it is the target/goal and D4 being the first item that needs to be filled out before the comparison would begin for the remaining cells.

Kind regards,
Priv

 

[privxls]

Hi FDibbins,

I'm experimenting on this one now, I will post the results in a bit. Thank you!

 

[Peter_SSs]

Do these look right? In particular, what about cell D8 that doesn't seem to meet any of your written conditions.

For cell D4 I manually formatted as yellow and then applied just one CF rule as shown below.
Then selected D5:Dxx and applied the other rules.

Excel Workbook
	D
3	85.00%
4	89.00%
5	86.33%
6	84.12%
7	84.13%
8	84.13%
9	87.00%
10
11
CF privxls

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4	1. / Formula is =D4	Abc
D5	1. / Formula is =AND(D5<>"")	Abc
D5	2. / Formula is =AND(D5D4)	Abc
D5	3. / Formula is =AND(D5>=D$3,D5	Abc
D5	4. / Formula is =AND(D5>=D$3,D5>D4)	Abc

 

[Marcelo Branco]

Peter,

The formulas are truncated. It seems HTML Maker is not working well.

M.

 

[Marcelo Branco]

Peter,

The formulas are truncated. It seems HTML Maker is not working well.

M.

You're not using HTML Maker. Is it Excel Jeanie?

M.

 

[Peter_SSs]

Peter,

The formulas are truncated. It seems HTML Maker is not working well.

M.

Thanks Marcelo - I should have known to check for that, especially since I had already edited the OP's post to fix the exact same problem!
It isn't the HTML maker (yes, jeanie) but the usual problem with the forum when a < sign is immediately followed by a letter.
I have edited the post to fix.
Thanks again for picking it up.

 

[privxls]

Thanks so much everyone, this has resolved my concern, props to Peter_SSs and also to FDibbins for the help regarding the conditions / formulas and thanks to Marcelo too for pointing out the typo/error with the format
Much appreciated guys