Conditional formatting color based on the result of a cell containing a formula

[silver37]

I need to conditionally format a cell based on the value of another cell which contains a formula.

I have columns headed as follows:

Goods Pack Size Shop 1 price/100g Shop 2 price/100g Shop 3 price/100g Cheapest price
apples 1kg 2.50 =2.5/10 4.20 =4.20/10 6.30 =6.30/10 MIN(D2,F2,H2)


Using conditional formatting, I want to be able to highlight the "Cheapest price" cell based on the cheapest price per 100g of the three shops:
Shop 1 = blue
Shop2 = green
Shop 3 = red

However, when I use the VALUE option (i.e. the value of the cheapest price column is equal to the value in Shop 1 price/100g) it doesn't fill the cell as the value of that cell is a formula not a number.

This is obviously a very simple version of a much larger sheet so I can't really add columns or paste values instead of formulas.

Any ideas?

 

[jtakw]

I need to conditionally format a cell based on the value of another cell which contains a formula.

I have columns headed as follows:

Goods Pack Size Shop 1 price/100g Shop 2 price/100g Shop 3 price/100g Cheapest price
apples 1kg 2.50 =2.5/10 4.20 =4.20/10 6.30 =6.30/10 MIN(D2,F2,H2)


Using conditional formatting, I want to be able to highlight the "Cheapest price" cell based on the cheapest price per 100g of the three shops:
Shop 1 = blue
Shop2 = green
Shop 3 = red

However, when I use the VALUE option (i.e. the value of the cheapest price column is equal to the value in Shop 1 price/100g) it doesn't fill the cell as the value of that cell is a formula not a number.

This is obviously a very simple version of a much larger sheet so I can't really add columns or paste values instead of formulas.

Any ideas?

Hi,

I'm not understanding all your requirements, but for CF highlight the cell containing the cheapest price, select your D, F, H column data, CF using formula:

=D2=$I2

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Goods	Pack Size	Shop 1	price/100g	Shop 2	price/100g	Shop 3	price/100g	Cheapest price
2	apples	1kg	2.5	0.25	4.2	0.42	6.3	0.63	0.25
Sheet1

Cell Formulas
Range	Formula
D2	=C2/10
F2	=E2/10
H2	=G2/10
I2	=MIN(D2,F2,H2)

 

[silver37]

Hi,

I'm not understanding all your requirements, but for CF highlight the cell containing the cheapest price, select your D, F, H column data, CF using formula:

=D2=$I2

Excel 2010

	A	B	C	D	E	F	G	H	I
Goods	Pack Size	Shop 1	price/100g	Shop 2	price/100g	Shop 3	price/100g	Cheapest price
apples	1kg

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.5[/TD]
[TD="bgcolor: #00B0F0, align: right"]0.25[/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"]0.42[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]0.63[/TD]
[TD="align: right"]0.25[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=C2/10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=E2/10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=G2/10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=MIN(D2,F2,H2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi there

Thank you for your reply.

It's the'Cheapest Price' column that I need to highlight based on the values returned in the three 'Price/100g' columns.

So, if the cheapest price/100g is
shop 1 - turn the 'Cheapest Price' cell blue
shop 2 - turn the 'Cheapest Price' cell green
shop 3 - turn the 'Cheapest Price' cell red

Hope that makes a better sense?

Thanks
Silver

 

[jtakw]

OK, I see:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Goods	Pack Size	Shop 1	price/100g	Shop 2	price/100g	Shop 3	price/100g	Cheapest price		CF Formulas for Column I
2	apples	1kg	2.5	0.25	4.2	0.42	6.3	0.63	0.25		Blue	TRUE
3											Green	FALSE
4											Red	FALSE
Sheet1

Cell Formulas
Range	Formula
L2	=I2=D2
L3	=I2=F2
L4	=I2=H2

You need 3 separate CF rules for I2, so CF I2, using formula (as indicated above in L2, L3, L4), Format Fill as desired.
Then you copy the CF formatting down Column I with "Format Painter"

 

[silver37]

OK, I see:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Goods	Pack Size	Shop 1	price/100g	Shop 2	price/100g	Shop 3	price/100g	Cheapest price	CF Formulas for Column I
apples	1kg	Blue
Green
Red

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.5[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"]0.42[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]0.63[/TD]
[TD="bgcolor: #00B0F0, align: right"]0.25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]=I2=D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]=I2=F2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L4[/TH]
[TD="align: left"]=I2=H2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You need 3 separate CF rules for I2, so CF I2, using formula (as indicated above in L2, L3, L4), Format Fill as desired.
Then you copy the CF formatting down Column I with "Format Painter"

Brilliant! Thank you jtakw Much appreciated!

 

[jtakw]

Brilliant! Thank you jtakw Much appreciated!

You're welcome, welcome to the forum.