Multiple Conditional Formatting in Excel

privxls

New Member
Joined
Nov 22, 2016
Messages
47
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

 
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,095
Office Version
365
Platform
Windows
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?
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Nov 22, 2016
Messages
47
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

New Member
Joined
Nov 22, 2016
Messages
47
Hi FDibbins,

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,095
Office Version
365
Platform
Windows
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
385.00%
489.00%
586.33%
684.12%
784.13%
884.13%
987.00%
10
11
CF privxls
#VALUE!
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Peter,

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

M.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,095
Office Version
365
Platform
Windows
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! :oops:
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

New Member
Joined
Nov 22, 2016
Messages
47
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 :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,802
Messages
5,482,986
Members
407,371
Latest member
Ernest F Mink

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top