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))&"%"

Use the below formula and just format the cell as PERCENTAGE. As it is, your
formula is returning a TEXT string which is why you can't get the
conditional formatting to work.

So, format cell A2 as PERCENTAGE, - FormatCellsNumber tabPERCENTAGE

Then use this formula:

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

Then, your conditional formatting is:

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

--
Biff
Microsoft Excel MVP


"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?