change display value but leave entered value same

No expert but I don't think that's possible. However, if you're running code then what's wrong with:

If range("a1").value = "Yes" then VARIABLE = 1

Thanks Swoop.

There is probably nothing wrong with it but sadly I'm a newbie when it comes to VBA and have no idea where to put it lol
Trying to find the code I originally found on a forum somewhere, but I feel I may have not saved it

Originally Posted by swoop99

No expert but I don't think that's possible. However, if you're running code then what's wrong with:

If range("a1").value = "Yes" then VARIABLE = 1

Sorry, my post was a little vague.

I have two, yes and no.

Currently @ work we are managing all our orders, stock etc via an excel workbook.
We would like to add some conditional formatting using icon sets, bars etc but have found that excel doesn't allow you to set criteria against 'text' data.
Some people are still using excel 2000 so we are unable to change the display values to 1/2 as we need to accommodate for all users and 1/2 will make little sense to many using excel 2000.
I thought if it was possible to change the display text, this would allow the people who are using 2007 up to view the conditional formatting as required and the rest to be able to view the data as required.

Originally Posted by Andy Pope

how many different number/texts do you have?

Why do you need to store 1 and display Yes. Maybe you could explain the purpose of your approach.

What Andy said.

It may be easier for you to have the "1" stored in another cell (in white so users can't see it) and have an "if" statement in the cell you want a "yes".

hi cobbswobble, do you mean sort of that? If yes it can be done with user-defined format, check attachment.

That's exactly what I wanted.

Thanks for the help! really appreciated!

Originally Posted by Andy Pope

Explanation helped, thank you.

So like this then, see attached.