Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color when goal is reached
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color when goal is reached
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! "-Bryan" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color when goal is reached
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 "BlueWolverine" wrote in message ... 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! "-Bryan" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color when goal is reached
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 "BlueWolverine" wrote: 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! "-Bryan" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color when goal is reached
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 "David Biddulph" wrote: 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 "BlueWolverine" wrote in message ... 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! "-Bryan" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color when goal is reached
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! "-Bryan" wrote: 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 "David Biddulph" wrote: 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 "BlueWolverine" wrote in message ... 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! "-Bryan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing font or fill-color when using formulae | Excel Discussion (Misc queries) | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
changing color of font to words in a list | Excel Discussion (Misc queries) | |||
Changing Font Color | Excel Discussion (Misc queries) | |||
changing font color for new text | Excel Discussion (Misc queries) |