Conditional Formatting Confusion...

[frakincaprica]

What I am trying to do seems simple to me.

I have a forumla in A2. A2 = B3-B2
I want to know when A2 is not equal to a value in F2. (Red)
I want to know when A2 is equal to a value in F2. (Green)
F2 is a number selected from a VLOOKUP function (referencing a table in
Sheet2), that is determined by a pull down list (also referencing sheet2).

So I select a value from my pull down list, and F2 corresponds to a number
from the table.

It seems like I can use the "cell value is" condition and it works to tell
me something like A2 "greater than" F2 (red). And I tried A2 "less than" F2
(green). And that worked. But it gets confused when A2 = F2 and makes it
green (which isn't mathematically possible, if A2 = F2 it's not "less than"

I've also tried this with "Formula Is" =AND(A2<>F2) (red) =AND(A2=F2)
(green), result was always red. I tried testing =AND(A2>F2) (red) and
=AND(A2<F2) (green), works except when A2=F2, it stays green even though that
is not a true condition A2=F2 does not meet the parameter of being "less
than" F2.

I also just tried using a reference cell (instead of my VLOOKUP cell) in
case that was throwing me. Just put a number in the cell and tried it. Same
results.

Any help is appreciated. I've been messing with this for 2 hours and
absolutely cannot figure it out despite looking at examples, excel help, and
trying everywhich way. I'm in excel 2003.

 

[Ms-Exl-Learner]

Place the cursor in A2 cell and press Cntrl+Spacebar. Now the total A column
will be getting selected and the A2 will be the Active cell (Active Cell will
have a white Background after selection).

Format>>Conditional Formatting>>Condition 1>>Formula Is>>
=AND($A2<>"",$A2<>$F2)
Then Click the Format command button and choose your desired Font and
Pattern colour.

Press Add Command button to add another Condition
Condition 2>>Formula Is>>
=AND($A2<>"",$A2=$F2)
Then Click the Format command button and choose your desired Font and
Pattern colour.

Click Ok…

 

[David Biddulph]

If you are getting Green having set "less than" as the condition, then I
would guess that you haven't actually got A2 = F2, and that you may be
displaying fewer digits than you've actually got, and have confused yourself
with rounding in the display. Look to see whether the formula =A2=F2
returns TRUE or FALSE, and see what the formula =A2-F2 shows (format the
result as General, not as number with only a few decimal places).
I assume that if you change F2 temporarily to =B3-B2 (the same as A2), you
no longer get Green?