Changing font color when goal is reached

[-Bryan]

I have created a form to track moisture content in a material. Depending upon
the material, a different standard is set, and when the moisture content
reaches within 4 points of the standard, the job is done.

Readings are entered daily in a row, and the standard is entered at the end
of the row. Is there a way to have the font color change when the data
entered is +/- 4 points of the standard?

Basically, the daily readings are input in A1,B1,C1...G1, and the standard
is entered in H1

 

[BlueWolverine]

For Excel 2003

Select the cells you care about (A1 to G1).
Go to Format --> Conditional Formatting.
change the deafult from "Cell Value is" to "Formula is"

the formula you want is going to be
=if(OR(A1>=.96*$H1,A1<=1.04*$H1),1,0)
Then go into the Format button and change the settings so that the format is
what you want it to look like when the value hits +/- 4%.

hit ok and it should go.

YOu can add up to 3 conditions in Excel 2003.

You can do SO much more with EXCEL 2007 it's unbelievable but I don't have
it in front of me and I don't have it memorized.
Good Luck,

 

[-Bryan]

Thanks Blue. I tried it (I have 2007) by selecting the cells, hitting the
conditional formatting on the ribbon, going to more rules, selecting formula
and inputting the formula. For some reason, all data input is changed in
color. I copy and pasted the formula you wrote, but there must be something I
did wrong. I get the gist of what I'm supposed to do, so I'll keep trying. If
anyone knows how to do it in '07, please let me know

 

[-Bryan]

Thanks guys, I fiddled around with the additional formatting rules and got
what I wanted using a variation of your formula (=and(a1<=h1+4).

I do have another question, I found the little symbols(flags, stop lights,
etc.), but it won't let me use them unless I use their pre-existing rules.
Using formulas, I can only change the basic cell formatting. Is there a way
to apply the icons and still use formulas for formatting? I'm trying to use a
green flag for any value <=standard+4, yellow flag for >standard+4,
<=Standard+10 and red flag for >=standard+11

Firstly, I think you intended to say AND, not OR. [With OR, the condition
would always be satisfied.]
Secondly you don't need the IF.

Try
=AND(A1>=.96*$H1,A1<=1.04*$H1)
--
David Biddulph

For Excel 2003

Select the cells you care about (A1 to G1).
Go to Format --> Conditional Formatting.
change the deafult from "Cell Value is" to "Formula is"

the formula you want is going to be
=if(OR(A1>=.96*$H1,A1<=1.04*$H1),1,0)
Then go into the Format button and change the settings so that the format
is
what you want it to look like when the value hits +/- 4%.

hit ok and it should go.

YOu can add up to 3 conditions in Excel 2003.

You can do SO much more with EXCEL 2007 it's unbelievable but I don't have
it in front of me and I don't have it memorized.
Good Luck,

 

[BlueWolverine]

Yep definitely meant an AND. I had never thought of it, but I suppose the
AND would return the 1 for you.



--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Thanks guys, I fiddled around with the additional formatting rules and got
what I wanted using a variation of your formula (=and(a1<=h1+4).

I do have another question, I found the little symbols(flags, stop lights,
etc.), but it won't let me use them unless I use their pre-existing rules.
Using formulas, I can only change the basic cell formatting. Is there a way
to apply the icons and still use formulas for formatting? I'm trying to use a
green flag for any value <=standard+4, yellow flag for >standard+4,
<=Standard+10 and red flag for >=standard+11

Firstly, I think you intended to say AND, not OR. [With OR, the condition
would always be satisfied.]
Secondly you don't need the IF.

Try
=AND(A1>=.96*$H1,A1<=1.04*$H1)
--
David Biddulph

For Excel 2003

Select the cells you care about (A1 to G1).
Go to Format --> Conditional Formatting.
change the deafult from "Cell Value is" to "Formula is"

the formula you want is going to be
=if(OR(A1>=.96*$H1,A1<=1.04*$H1),1,0)
Then go into the Format button and change the settings so that the format
is
what you want it to look like when the value hits +/- 4%.

hit ok and it should go.

YOu can add up to 3 conditions in Excel 2003.

You can do SO much more with EXCEL 2007 it's unbelievable but I don't have
it in front of me and I don't have it memorized.
Good Luck,

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


:

I have created a form to track moisture content in a material. Depending
upon
the material, a different standard is set, and when the moisture content
reaches within 4 points of the standard, the job is done.

Readings are entered daily in a row, and the standard is entered at the
end
of the row. Is there a way to have the font color change when the data
entered is +/- 4 points of the standard?

Basically, the daily readings are input in A1,B1,C1...G1, and the
standard
is entered in H1