Format an autoshape dependent on formula

[Kim Duffy]

Traffic-light Effect

In cell C1 the formula reads =IF(A1<B1,"Green", IF(A1<B1/0.95,"Red","Amber"))

I'd like to place/attach an autoshape to cell C1 which will change colour
dependent on the result of the formula in cell C1, rather than have the word
Red, Amber or Green appear. Visually more effective.

If I insert rows will the autoshape move with its associated row?

Please note I'm no guru, and will need a clear explanation.

 

[Kim Duffy]

It might be easier to apply the colour directly to the cell rather than an
autoshape. I await your replies.

 

[Debra Dalgleish]

You can use conditional formatting to colour the cell.

Select cell C1
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:
=A1<B1
Click the Format button, and on the Pattern tab,
select the Green colour
Click OK
Click the Add button

From the first dropdown, choose Formula Is
In the formula box, type:
=A1<B1/0.95
Click the Format button, and on the Pattern tab,
select the Red colour
Click OK
Click the Add button

From the first dropdown, choose Formula Is
In the formula box, type:
=OR(A1<>"",B1<>"")
Click the Format button, and on the Pattern tab,
select the Yellow colour
Click OK, click OK

Or, you could omit the third condition, and colour the cell yellow.