Conditional formatting - joining number and text

  • Hi,

    I have been having a hard time finding out how to do this, and would greatly appreciate any help on how to do it, either with or without VBA. Here is what I need to do:

    In column C, the user can enter a number choice between 1 and 6. If he enters 1, then I need the same cell to be formatted to read "1 - EOI", i.e. take the number he entered and add some text to it.

    Correspondingly the other values would be:

    2 -> 2 - Shortlisted
    3 -> 3 - Proposal
    4 -> 4 - Lost
    5 -> 5 - Contract
    6 -> 6 - Completed

    I tried to use custom format, but can only enter 3 options. I need 6. I also tried to use Conditional Formatting, but couldn't find a way to give number formats given a certain condition.

    Any ideas as to how this can be solved???

  • You can do this with Select case in VBA, if you need help we will need your full list of conditions. Dave has an example Workbook in his Workbook downloads

  • Hi Peppy

    Welcome to

    Right click on the Sheet name tab and select View Code. In here paste this;

  • a VBA approach could look something like this

    Place the code in the relevant workshhet code module (accessed by right clicking the worksheet tab & selecting "view code")

    Edit: Dave is on fire today :)

  • Hi,

    Wow, thanks for fast reply. This forum is really great. I pasted in Wills code and it worked beautifully for cell A1. However, if I want this to apply to the whole column...what do I put in instead of A1?


  • Hi again Will.

    I tried Daves code so that it would test whether the number is between 1 and 6, but could not get that code snippet to work. It looks like it is doing something, but the text is not added to the number...any ideas?

  • Try this amalgamation of both examples

    Worked on my tes

  • Hi,

    here's another one

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!