Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a cell's color based on return value of a formula
A B C In this example on the left, I have set the "C"
column to be a 1 5 0 5 formula (=a1+b1), with this formula used in all rows. My font 2 5 -1 4 color for the entire spreadsheet is black. I want the value in the "C" column to change to "RED" if the value in "C1" (after the formula is applied) is less than "A1". If the value in"C1" is equal to "A1", change the color to "BLUE". I went in and used the conditional formatting option. For cell "C1" I had the conditional format reading: Condition 1 (cell "C1") "Cell Value is equal to A1" with the color set to "BLUE" (These conditions have been applied to the whole "C" column) When I get the "C1" value equal to "A1", no change occurs. Condition 1 (still cell "C1") "Cell Value is less than A1" with the color set to "RED" (These conditions have been applied to the whole "C" column) When I get the "C1" value equal to "A1", color changes to "RED" What I don't understand is the value in "C1" is equal to "A1", but the color is only changing if the conditional format is set to "less than". I guess I need help understanding what the program is doing. Help would be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a cell's color based on return value of a formula
Since your values are calculated, you many be getting rounding errors.
Remember that displayed value and stored value are not the same thing. See http://www.cpearson.com/excel/rounding.htm http://www.mcgimpsey.com/excel/pennyoff.html You could work around that with CF1: Formula is =ABS(C1-A1)<0.0000001 with an arbitrarily small comparison value, depending on the expected magnitude of your numbers. In article , "Gee-off" wrote: A B C In this example on the left, I have set the "C" column to be a 1 5 0 5 formula (=a1+b1), with this formula used in all rows. My font 2 5 -1 4 color for the entire spreadsheet is black. I want the value in the "C" column to change to "RED" if the value in "C1" (after the formula is applied) is less than "A1". If the value in"C1" is equal to "A1", change the color to "BLUE". I went in and used the conditional formatting option. For cell "C1" I had the conditional format reading: Condition 1 (cell "C1") "Cell Value is equal to A1" with the color set to "BLUE" (These conditions have been applied to the whole "C" column) When I get the "C1" value equal to "A1", no change occurs. Condition 1 (still cell "C1") "Cell Value is less than A1" with the color set to "RED" (These conditions have been applied to the whole "C" column) When I get the "C1" value equal to "A1", color changes to "RED" What I don't understand is the value in "C1" is equal to "A1", but the color is only changing if the conditional format is set to "less than". I guess I need help understanding what the program is doing. Help would be appreciated. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a cell's color based on return value of a formula
Thanks, but how do I input your "=ABS(C1-A1)<0.0000001" over a formual that
already exists in that cell? "JE McGimpsey" wrote: Since your values are calculated, you many be getting rounding errors. Remember that displayed value and stored value are not the same thing. See http://www.cpearson.com/excel/rounding.htm http://www.mcgimpsey.com/excel/pennyoff.html You could work around that with CF1: Formula is =ABS(C1-A1)<0.0000001 with an arbitrarily small comparison value, depending on the expected magnitude of your numbers. In article , "Gee-off" wrote: A B C In this example on the left, I have set the "C" column to be a 1 5 0 5 formula (=a1+b1), with this formula used in all rows. My font 2 5 -1 4 color for the entire spreadsheet is black. I want the value in the "C" column to change to "RED" if the value in "C1" (after the formula is applied) is less than "A1". If the value in"C1" is equal to "A1", change the color to "BLUE". I went in and used the conditional formatting option. For cell "C1" I had the conditional format reading: Condition 1 (cell "C1") "Cell Value is equal to A1" with the color set to "BLUE" (These conditions have been applied to the whole "C" column) When I get the "C1" value equal to "A1", no change occurs. Condition 1 (still cell "C1") "Cell Value is less than A1" with the color set to "RED" (These conditions have been applied to the whole "C" column) When I get the "C1" value equal to "A1", color changes to "RED" What I don't understand is the value in "C1" is equal to "A1", but the color is only changing if the conditional format is set to "less than". I guess I need help understanding what the program is doing. Help would be appreciated. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a cell's color based on return value of a formula
I meant to use that formula in your Conditional Format (i.e., "CF").
In article , "Gee-off" wrote: Thanks, but how do I input your "=ABS(C1-A1)<0.0000001" over a formual that already exists in that cell? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a cell's color based on return value of a formula
Use of Conditional Formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm http://www.mcgimpsey.com/excel/conditional6.html http://www.datapigtechnologies.com/ExcelMain.htm (videos, look for word "Conditional") --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JE McGimpsey" wrote in message ... I meant to use that formula in your Conditional Format (i.e., "CF"). In article , "Gee-off" wrote: Thanks, but how do I input your "=ABS(C1-A1)<0.0000001" over a formual that already exists in that cell? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return adjacent cell's value based on formula | Excel Discussion (Misc queries) | |||
Formula to populate data in a cell based on another cell's color | Excel Discussion (Misc queries) | |||
CHANGE ONE CELL'S VALUE AND GET THE SAME RETURN - SOME HELP PLEASE | Excel Discussion (Misc queries) | |||
Have row color change to one of 8 different colors based on one cell's value (I, O, C, T, L, E, X, A) | Excel Discussion (Misc queries) | |||
Change value based on another cell's value | Excel Discussion (Misc queries) |