Thanks guys, that totally makes sense. I originally had everything set up
with the vlookup column formatted as percentage and the cell, but then the
data came from another program and C&P'ing it in made 100% go to 10000%. Your
vlookup/100 solution is so much more elegant than mine! Thanks!

And so is your conditional formula solution. Thanks!
You guys rock!

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