conditional formula to change color if not =

[Brandi Reese]

I need a formula to make a total show up in another color
if it is not equal to another cell. How do I do this?

 

[David Hager]

For B1:

=a1<>b1

David Hager
Excel MVP

 

[Dan E]

Brandi,

Select the cell you want to change color, from the menu:
Format -> Conditional Formatting
Change Condition 1 to:
Cell Value Is not equal to
Put your cursor in the final entry field and select the cell
you want to compare to... (ie =$C$1)
Set the format you want when the cells aren't equal

Your done...

One thing to not is that their is no margin of error for this.
If one cell is 300 and the other is 300.0000001 the cell
color will still change.

To include a margin of error. (Example leave +or- a penny)
Format -> Conditional Formatting
Change Condition 1 to:
Cell Value Is not equal to =$C$9 - 0.01 =$C$9 + 0.01

Dan E

 

[ed]

Brandi,

Select the cell you want to change color, from the menu:
Format -> Conditional Formatting
Change Condition 1 to:
Cell Value Is not equal to
Put your cursor in the final entry field and select the cell
you want to compare to... (ie =$C$1)
Set the format you want when the cells aren't equal

Your done...

One thing to not is that their is no margin of error for this.
If one cell is 300 and the other is 300.0000001 the cell
color will still change.

To include a margin of error. (Example leave +or- a penny)
Format -> Conditional Formatting
Change Condition 1 to:
Cell Value Is not equal to =$C$9 - 0.01 =$C$9 + 0.01

Dan E

Dan: See Excel Help for the function ROUND ed


Brandi: Use Conditional Formatting Formula is,, =SUM(a1:a29)<>Q8
Formatting is Font pick a color. You might want to use the
Function ROUND in the formula also. Ed

 

[Dan E]

"Dan E" <[email protected]> wrote in message news:<[email protected]>...

Dan: See Excel Help for the function ROUND ed

Ed,

Let's look at the situation a bit
I have a cell say A2 which has a total in it...
The total is 300.00001 (small error), I would like to compare it
to another cell say A1 which has 300 in it.
I want cell A2 to be in red if it doesn't equal A1 (but only within
a penny (.01).

How on earth will the round formula help me when using
conditional formatting???
If I use Cell Value Is not equal to =ROUND(A1,1)
I would be rounding 300 to 300.00 which is still not equal to
300.00001...
Rounding doesn't work in this case unless I input
Formula Is =ROUND((insert total formula),2) = $A$1
but that will compare it to the penny (not +or- a penny)

Dan E

 

[J.E. McGimpsey]

One way:

CF1: =ROUND(ABS(A1-A2),2)>0.01 ===> red

 

[ed]

One way:

CF1: =ROUND(ABS(A1-A2),2)>0.01 ===> red

J.E. beat me to it. I guess I should have referred you to ABS also. ed