Conditional Formatting - a bit of a weird one

[fat controller]

I am trying to conditionally format a cell based on the value in another cell - however, there is a variable that will change the way I want the conditional formatting to work.

The cell that the formatting is set to react to contains a number to two decimal places.

• If that number is a low number (eg 1.20 or 1.50) then I want the formatting of the adjacent cell to give red text if the number therein is larger, and green if it is lower.
• If that number is a high number (60.00 and above) then I want the formatting of the adjacent cell to go red if the number contained therein is lower, and green if it is higher

Is this possible?

 

[arnelgp]

highlight the colum you want to see the formatting.
you need two conditional format.

For Red:
=OR(AND(A1>=1.2, A1<=1.5), A1<60)

For Green:
=OR(A1<1.2, A1>=60)

 

[fat controller]

I have tried those on once cell, but it does not appear to work - I have changed A1 for the relevant cell that it should be comparing to (L44 in this case), and currently L44 has a value of 1.30.

No matter what I enter in cell M44 (the cell that I have applied the rules to). the text remains red

I almost certainly worded the question wrongly - maybe this will give more of a clue.

L44 is a variable (links to another sheet), and if it is 0 then the cell is blank.

If L44's value is between 0.01 and 3.00 (these numbers should give scope to cover all eventualities), then I would like the text in M44 to go green if the value in cell M44 is less than the value in L44 or red if the value in M44 is greater than the value in M44.

If L44's value is greater than 4.00, then I would like the text in M44 to go green if it is greater than the value of L44, and red if the value in M44 is less than the value of L44.

 

[arnelgp]

should work even if linked cell.

for red:
=AND(OR(AND(A1>=1.2, A1<=1.5), A1<60),NOT(ISBLANK(A1)))

for green:
=AND(OR(A1<1.2, A1>=60),NOT(ISBLANK(A1)))

 

[fat controller]

I am clearly getting something wrong here, sorry to be a pain.

For red, I have:
=AND(OR(AND($L$44>=1.2, $L$44<=1.5), $L$44<60),NOT(ISBLANK($L$44)))

and for green, I have,
=AND(OR($L$44<1.2, $L$44>=60),NOT(ISBLANK($L$44)))

L44 is currently showing a value of 1.30, cell is formatted as a number to two decimal places, yet no matter what value I put into M44 (the cell that has the conditional formatting), the text remains red.

 

[arnelgp]

sorry my friend i overlook your post M44 cell should be included.
these is based on your second post:

For red
=AND(OR(AND($L$44>=1.2, $L$44<=1.5, $L$44<$M$44), $L$44<60),NOT(ISBLANK($L$44)))

and for green
=AND(OR(AND($L$44<1.2,$L$44>=$M$44), $L$44>=60),NOT(ISBLANK($L$44)))

 

[fat controller]

Thank you

I am at a different PC at the moment, but will give this a shot later on and let you know how it goes.

I must admit that I haven't really used Excel for anything more than the very basic stuff, so apparently there is another thing I need to add to my learning path....

 

[fat controller]

Some progress, but something still not playing nicely. Green only works if the value of L44 is greater than 60 (I have also tried the rules independently also), but the formatting does not change when it drops below 60.

When L44 is the lower value, M44 is red regardless of the value that is entered into it.

 

[arnelgp]

please guide me with your rules again:

L44------------------------M44-------------------------------------COLOR
1.2-1.5-------------------value is less or equal to L44---------green
1.2-1.5-------------------value is greater than L44--------------red
60 and above------------value is less than or equal L44-------red
60 and above------------value is greater than L44-------------green

is this correct?

 

[fat controller]

please guide me with your rules again:

L44------------------------M44-------------------------------------COLOR
0.2-3.5-------------------value is less or equal to L44---------green
0.2-3.5-------------------value is greater than L44--------------red
50 and above------------value is less than L44-------red
50 and above------------value is greater or equal to L44-------------green

is this correct?

Pretty much - I have changed a couple of the figures above, (thinking ahead, 0.2-20.5 for the lower range would prevent issues further along the line, and anything above 50 for the higher range.

With the higher range, I think it is probably best to make it that the value is greater than or equal to L44 and less than (that is not majorly important, so it can be left as you had it)

Thanks for your patience with this (and me!)

 

[arnelgp]

hopefully we can get it:

for green:
=OR((AND(L44>=1.2, L44<=1.5, M44<=L44)), (AND(L44>60, M44>L44)))

for red:
=OR(AND(L44>=1.2, L44<=1.5, M44>L44), AND(L44>60, M44<=L44))

happy weekend.

 

[fat controller]

You sir are a genius! Works perfectly

I really appreciate all your time, thank you