Red Amber Green Help Conditional Formatting

[Clelv]

Hi All,

I'm struggling with a query on Excel.

I have some cells which i would like to change colour to Red amber and Green depending on where the number falls within a reference table

So my information table will automatically change Cell A2, B2, C2, D2 to red, amber or green depending on where that number falls within table 2 - Reference data

Table 1

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Temple[/TD]
[TD]Sun[/TD]
[TD]Chapel[/TD]
[TD]Yard[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]RED[/TD]
[TD][/TD]
[TD]AMBER[/TD]
[TD][/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD]MAX[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Temple[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sun[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Chapel[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Yard[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]23[/TD]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]

I have 3 formulas in conditional formatting for each of the four cells in table 1. I've been using:

For cell A2

=and(A2>=B3,A2<=C3) Colour Red
=and(A2>=D3,A2<=E3) Colour Amber
=and(A2>=F3,A2<=F3) Colour Green

And i do this 3 times for each of the cells in the information table 1 = A2, B2, C2 & D2.

This works sometimes but not always and i'm totally confused as to why that is!

Any help will be welcomed. Thanks in advance

 

[Gerald Higgins]

Can you give us details of an example where it does NOT work ?
And when you say it does not work, what exactly does it show ?

 

[Clelv]

Hi,

Thank you for responding.

It doesn't work when the number in cell B2 and D2 in Table 1 is the "From" Figure in Table 2. E.g 6 and 16. It doesn't return any colour, it just remains blank. But this condition works fine in the other cells in Table 1 and if i change the figure to 7 or 17 in Table 1 then the condition works.

The table above obviously isn't the table i'm using, and that table picks the figures in table 1 up from another worksheet in the same workbook. I get my cells to format using the original location of the data on the other worksheet to cut down on links etc.

The spreadsheet updates it'self each week and it seems that some weeks they all return correctly and then another time they don't.

I really just want to know if there should be any order to my conditional formatting formula's and if they ones i'm using SHOULD work and then i can go over it with a fine tooth comb (again!) and try to see where the fault is.

Thanks for all your help, it's driving me potty

 

[Clelv]

Hi,

Looking into this further, it seems that the conditional formatting doesn't like me working my conditions out from a figure that has an average formula behind it. If i over type the formula it works fine, if i replace it with the average formula it returns no colour. This is confusing though because it works fine on the 3 out of 5 cells, all using an average formula.

Thanks again

 

[Joe4]

Try rounding your values. Excel has a little problem with floating arithmetic errors that sometimes rears its ugly head.
See: https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

So you may want to try re-writing your formulas like:

[COLOR=#333333]=and(ROUND(A2,0)>=ROUND(B3,0),ROUND(A2,0)<=ROUND(C3,0))[/COLOR]

That will round each value off to zero decimals. Change it as you see fit.

 

[Clelv]

Amazing. Thank you.

Really clever help and now i'm super pleased with the outcome now so thank you

I half wondered if it was to do with the number not being whole but wouldn't of known how to correct it.

This site has been really useful to me over the last 2yrs!!

 

[Joe4]

Excellent! Glad we were able to help!