Conditional formatting based on adjacent cell values

**dberliner** · 07-28-2011, 05:37 AM

Hello and thanks in advance for helping me out!

I have a set of 4 columns ( C to F) . I want the cell in C1 to be highlighted in a specific color (say red) if its value is LOWER THAN ALL the other values in D1 to F1). On the other hand, I want the cell in C1 to be highlighted another color (say green) if its value is HIGHER THAN ALL the other cell values in D1 to F1.

I've tried different methods but have been unable to accomplish this.

Thanks !

D. Berliner

**sunflowers** · 07-28-2011, 05:53 AM

You can enter this fomular in Condition Formating : Example : data : C1 : F3
Condition formatting 1 : $C1 < Min($D$1:$F$3) -> Red
Condition formatting 2 : $C1 > Max($D$1:$F$3) -> Blue (or green)he he!
You can See attache file (example) :

**dberliner** · 07-28-2011, 06:38 AM

Originally Posted by sunflowers

You can enter this fomular in Condition Formating : Example : data : C1 : F3
Condition formatting 1 : $C1 < Min($D$1:$F$3) -> Red
Condition formatting 2 : $C1 > Max($D$1:$F$3) -> Blue (or green)he he!
You can See attache file (example) :

Great , that worked!

However, I forgot to mention something important. I need to have this done all over the column by method of extrapolation ( i.e. Cells 2, 3, 4 and indefinitely...). How do I extrapolate the formula so that it does $C2 < Min($D$2:$F$2) -> Red , $C3 < Min($D$3:$F$3) -> Red, and so and so?

Right now if i apply the condition regularly to the other cells they all seem to be based on the original formula for the cell in row 1.

**sunflowers** · 07-28-2011, 12:31 PM

You can change that fomular in conditional Formating :
CF 1 : $C1 < Min($D1:$F1) -> Red
CF 2 : $C1 > Max($D1:$F1) -> Green

**dberliner** · 07-28-2011, 12:42 PM

Originally Posted by sunflowers

You can change that fomular in conditional Formating :
CF 1 : $C1 < Min($D1:$F1) -> Red
CF 2 : $C1 > Max($D1:$F1) -> Green

Hey sunflowers!

Sorry to be such a bother, would you mind posting again a spreadsheet example showing the extrapolation process? Didn't quite get the idea of how to implement that.

Thanks,

D. Berliner