You can avoid having the textual display of percentages by changing
your vlookup formula to:

=(VLOOKUP(A3,$O:$Z,12,FALSE))/100

and formatting the cell as percentage with 0dp.

So, your first two CF conditions would then be:

1: Formula Is: =A2=1
2: Formula Is: =A2<=0

And your 3rd condition can be:

Formula Is: =AND(A20,A2<1)

Hope this helps.

Pete

On Feb 5, 10:32*pm, Sue in AZ
wrote:
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?