Data is input in to cell E57.
A percentage is calculated, based on the input and returned to cell E58.

I used CF to compare this with cell E29
=$E$58$E$29
If the result is above that of E29, E58 turns red.

What I want to do is turn E58 yellow if the result is up to 10 below E29.

What I mean is if cell E29 = 100
E58 100 turn red
E58 is 90 to 99 turn yellow
E58 < 90 no format

You can add multiple conditional formats. Click the add button on the
conditional format dialogue box to get space for a second condition. Set
first condition to check if E58=100 (set format to red). Set second
condition to check if e58=90 (set format to yellow). Conditional formatting
only gets to the second condition if the first one isn't true, so E58=101
won't make it yellow as the first condition is already TRUE and formatting is
red. No need to set a condition for E58<90 as this will be the net result of
the first two both being FALSE.

Cheers,

Glen

"AndyB" wrote:

Data is input in to cell E57.
A percentage is calculated, based on the input and returned to cell E58.

I used CF to compare this with cell E29
=$E$58$E$29
If the result is above that of E29, E58 turns red.

What I want to do is turn E58 yellow if the result is up to 10 below E29.

What I mean is if cell E29 = 100
E58 100 turn red
E58 is 90 to 99 turn yellow
E58 < 90 no format

Hi Glen,
Sorry, I should have explained more, the problem is the compare isn't a
fixed value (100), its a variable in a cell.

I have
=$E$58$E$29 to turn E58 red
and
=SUM($E$29-10)<$E$58 but this turns E58 yellow whatever the value below E29

What I want it to do is take E29 and -10, then turn E58 yellow if it is
anywhere in that range, 1 to 9 below the value in E29

"Glen" wrote:

You can add multiple conditional formats. Click the add button on the
conditional format dialogue box to get space for a second condition. Set
first condition to check if E58=100 (set format to red). Set second
condition to check if e58=90 (set format to yellow). Conditional formatting
only gets to the second condition if the first one isn't true, so E58=101
won't make it yellow as the first condition is already TRUE and formatting is
red. No need to set a condition for E58<90 as this will be the net result of
the first two both being FALSE.

Cheers,

Glen

"AndyB" wrote:

Data is input in to cell E57.
A percentage is calculated, based on the input and returned to cell E58.

I used CF to compare this with cell E29
=$E$58$E$29
If the result is above that of E29, E58 turns red.

What I want to do is turn E58 yellow if the result is up to 10 below E29.

What I mean is if cell E29 = 100
E58 100 turn red
E58 is 90 to 99 turn yellow
E58 < 90 no format

It's in the direction of the signs, or something. I dunno why it works one
way and not the other, not enough coffee yet, but the following will work the
way you want...

=$E$58=$E$29 to turn E58 red
second condition
=E$58+10=$E$29 to turn E58 yellow

Cheers,

Glen

"AndyB" wrote:

Hi Glen,
Sorry, I should have explained more, the problem is the compare isn't a
fixed value (100), its a variable in a cell.

I have
=$E$58$E$29 to turn E58 red
and
=SUM($E$29-10)<$E$58 but this turns E58 yellow whatever the value below E29

What I want it to do is take E29 and -10, then turn E58 yellow if it is
anywhere in that range, 1 to 9 below the value in E29

"Glen" wrote:

You can add multiple conditional formats. Click the add button on the
conditional format dialogue box to get space for a second condition. Set
first condition to check if E58=100 (set format to red). Set second
condition to check if e58=90 (set format to yellow). Conditional formatting
only gets to the second condition if the first one isn't true, so E58=101
won't make it yellow as the first condition is already TRUE and formatting is
red. No need to set a condition for E58<90 as this will be the net result of
the first two both being FALSE.

Cheers,

Glen

"AndyB" wrote:

Data is input in to cell E57.
A percentage is calculated, based on the input and returned to cell E58.

I used CF to compare this with cell E29
=$E$58$E$29
If the result is above that of E29, E58 turns red.

What I want to do is turn E58 yellow if the result is up to 10 below E29.

What I mean is if cell E29 = 100
E58 100 turn red
E58 is 90 to 99 turn yellow
E58 < 90 no format