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.

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 <[email protected]>,
"Gee-off" <[email protected]> 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.

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 <[email protected]>,
> "Gee-off" <[email protected]> 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.
>

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

In article <[email protected]>,
"Gee-off" <[email protected]> wrote:

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

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" <[email protected]> wrote in message news:[email protected]...
> I meant to use that formula in your Conditional Format (i.e., "CF").
>
> In article <[email protected]>,
> "Gee-off" <[email protected]> wrote:
>
> > Thanks, but how do I input your "=ABS(C1-A1)<0.0000001" over a formual that
> > already exists in that cell?