Change a cell's color based on return value of a formula

[Guest]

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.

 

[JE McGimpsey]

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.

 

[Guest]

Thanks, but how do I input your "=ABS(C1-A1)<0.0000001" over a formual that
already exists in that cell?

 

[JE McGimpsey]

I meant to use that formula in your Conditional Format (i.e., "CF").

 

[David McRitchie]

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")