Why don't you simply format A2 where the lookup formula is as percent? So if
it returns 100, that will display as 100%, 50 will display as 50% and 0 as
0%
Then you can format the cell with =AND(A2<0,A2<100) When use AND you must
specify at least 2 conditions. In your =AND(Vlookup .....) you have only 1.


Tyro

"Sue in AZ" wrote in message
...
Hello Excel gurus!

I have a cell (Say A1) that I'm trying to conditionally format when the
adjacent cell (say A2)is between but not equal to 0 and 100 percent (I'm
trying to show when something is in progress, but not completed). I don't
want it to do anything at 0% or 100%.

Possibly complicating the issue, the value in the adjacent cell (A2) is a
vlookup of a another cell (say A3), returning number provided in 0-100
format. I want to display 0%-100% format, so my formula in A2 is something
like
=(VLOOKUP(A3,$O:$Z,12,FALSE))&"%"

To do it, I've set up three conditions.
1. Format (to do nothing) when
Formula Is =A2=100&"%"
2. Format (to do nothing) when
Formula Is =A2=0&"%"

To do the third one (the one that really matters), I've been searching the
boards and can't find a way to do it gracefully.

I currently have:
3. Format special when
Formula Is =AND(VLOOKUP(A3,$O:$Z,12,FALSE)<"100")

My problem is that it's weird and akward to vlookup the same thing again
(but I couldn't resolve the added "%" otherwise), but more troubling issue
is
that when the value is something greater than 100, it still formats it as
set
in condition 3. It works when any number other than 0 and 100, not just
numbers other than them and less than 100. That's just not
right....right?
Is my logic all weird?

Thanks for reading all the way through....any thoughts?