Conditional formatting using part of the cells contents

  • Thread starter Thread starter Duey
  • Start date Start date
D

Duey

Hi,
i'm doing some MPG calculations at the moment and using a formula from a
seperate worksheet which then displays on the first worksheet (="You average
"&Round(&Sheet2!A4,2)&" MPG") and i want to format that cell (that displays
the "Your Average is xx.xx MPG) so that if, for example, my average MPG is
<=10 then the whole cell is red, >10 <30 Yellow and >30 Green

i hope this makes sense...

Cheers for your help
 
Hi,
use conditional formating, where the formula is, in the first option enter

=A2<=10

choose green
2nd option

=And(A2>10,A2<30)
choose yellow

third option enter

=A2>30

choose red
 
you do need conditional formatting but you want to use 'formula is' and base
the formula on sheet2!a4 (or wherever you mpg figure is calculated) but
conditional formatting wont let you refer directly to a cell on another sheet
so the easiest way is to name that cell (in your example: select Sheet2!a4
and click insert, name, define, "MPG", ok) once this cell is named the
following will work:-

if your result is in sheet1!a1 select it, click format, conditional
formatting and change from 'cell value is' to 'formula is' then put =MPG<10,
set format to red
add another option and do =MPG>30, set format to red, add another option and
put =MPG<>0 and set format to yellow (this is the default so as long as the
other two arent true this will apply)

Hth

RegMigrant
 
Back
Top