Colour formatting based on Cell values

[davecoppins]

Hope you can help

I am trying to create a price comparison tool over 3 columns. Column A is the default supplier price and columns B & C are alternative supplier prices. What I would like to achieve is:
If the value of Column B2 or C2 is the same as Column A2 turn Cell Amber
If the value of Column B2 or C2 is greater than Column A2 turn Cell Red
If the value of Column B2 or C2 is less than Column A2 turn Cell Green

Thanks in advance

David

 

[Special-K99]

1. "turn cell..."

which cell
B2 C2 or A2 ?

2. "If the value of Column B2 or C2 is the same as Column A2 turn Cell Amber"
This contradicts your other conditions.

If the value of B2 is the same as A2, using an OR as you've stated then you havent checked the value of C2 so C2 could be greater than A2 which means both your first AND second condition are BOTH true and your second condition states the should be red (so which should it be Amber or Red?), or if C2 is less than A2 the third conditions says the cell should be green but that means the first and third conditions will BOTH be true (so which should it be Amber or Green?)

As an example
assume A2=3 B2=3 C2=1

First condition will turn the cell Amber (B2 = A2)
Second condition is false, will have no effect
Third condition will turn the cell Green (C2 < A2)

So what should the result be Amber or Green ?


What are you trying to achieve with this colour arrangement?

 

[davecoppins]

Thanks for the reply, let me try to clarify.

All figures in Column A will remain un-formatted as this is the target or default value

Cell A2 = 100 / Cell B2 = 101 (turn Cell red) / Cell C2 = 100 (turn Cell amber)
Cell A3 = 100 / Cell B3 = 99 (turn Cell green) / Cell C3 = 101 (turn Cell red)
Cell A4 = 100 / Cell B4 = 101 (turn Cell red) / Cell C4 = 99 (turn Cell green)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Supplier 1[/TD]
[TD]Supplier 2[/TD]
[TD]Supplier 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]£100[/TD]
[TD]£101[/TD]
[TD]£100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]£100[/TD]
[TD]£99[/TD]
[TD]£101[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]£100[/TD]
[TD]101[/TD]
[TD]99[/TD]
[/TR]
</tbody>[/TABLE]

Hope that makes better sense - can't upload images for some reason

Thanks in advance

David

 

[Special-K99]

This should work, (untested)

Select the column B AND C ranges in one go.

Conditional Formatting
New Rule
Use a formula to determine...
3 formulas needed

=(B2 = $A2) format as amber
=(B2 > $A2) format as red
=(B2 < $A2) format as green

 

[davecoppins]

Spot on

Many thanks for your help