Conditional formating

[timsims]

Hi,

I would like to do the conditional formatting in cell F10 so that if the value is more than the total of E20 it goes red and if it is within 2000 of the total of E20 it goes Orange.

Making it go red if it is more than I found easy but, how would I make it go Orange if it's within 2000 of the total?

Thanks

 

[T. Valko]

Hi,

I would like to do the conditional formatting in cell F10 so that if the value is more than the total of E20 it goes red and if it is within 2000 of the total of E20 it goes Orange.

Making it go red if it is more than I found easy but, how would I make it go Orange if it's within 2000 of the total?

Thanks

I used to work with a guy named Tim Sims!

Need a better definition of "within 2000". Does that mean +/- 2000? That could also satisfy the condition of "if the value is more than the total of E20".

 

[timsims]

I used to work with a guy named Tim Sims!

Need a better definition of "within 2000". Does that mean +/- 2000? That could also satisfy the condition of "if the value is more than the total of E20".

There was also a Tim Sims in Muriel's Wedding and the guy in the new Natwest advert is called Tim Sims, i never hear the end of it!

The number in cell E20 is usually around 4000, so, say it was 4000 then if the value in F10 got to 2000 it would go Orange and when it got to 4000 it would go red.

Please remember though that the number in E20 does change.

Thanks

 

[T. Valko]

There was also a Tim Sims in Muriel's Wedding and the guy in the new Natwest advert is called Tim Sims, i never hear the end of it!

The number in cell E20 is usually around 4000, so, say it was 4000 then if the value in F10 got to 2000 it would go Orange and when it got to 4000 it would go red.

Please remember though that the number in E20 does change.

Thanks

Ok, what version of Excel are you using?

 

[timsims]

ok, what version of excel are you using?

2007

 

[T. Valko]

2007

OK, try this...

For RED...

• Select cell F10
• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>
• Use a formula to determine which cells to format
• Enter this formula in the box below:
• =AND(COUNT(E20,F10)=2,F10>=E20)
• Click the Format button
• Select the desired style(s)
• OK out

For ORANGE...

Repeat the above but use this formula:

=AND(COUNT(E20,F10)=2,F10>=E20-2000,F10<E20)