[Solved] VBA: Custom Number Format Based on Cell Reference

  • Does anyone if it is possible to create macro that can create a custom number format for specific column based on a reference from a specific cell?


    Cell $A$1=Q1/03-

    Therefore Column Bs number format="Q1/03-"General


  • Hi

    I'm a little lost. Can you give an example of what is in a cell and what you want it to look like after the format.

    Also what is the relationship between where the cell is and what is it supposed to look like.


    There are three types of people in this world.
    Those who can count and those who can't.

  • Hi Neale,

    I have a Worksheets representing different a specific quarters so Q1/03 represents 'Quarter 1, 2003'

    The sheet has several rows. I need each row to have a unique reference. So Row 1 would show the reference 'Q1/03-A' for example just by typing the letter A.

    Hope this make sense.


  • Hi,
    If you were typing numbers in the cell, the custom format would work - -

    Format / Cells / Number / Custom , in the 'Type' box, enter "Q1/03-"# (be sure to enter the quote marks)

    If you then type a 4 into the cell, you would get Q1/03-4 in the cell. However, custom number formats have no effect if you are entering text (like the A in your example)

    For that you could use the concantenate function - but you'd need 2 cells - enter "A" in A1 and A2 would be =CONCATENATE("Q1/03-",A1) - - which probably doesn't help much.


  • Following on from rem1224's idea try the custom format

    then type in A or B. It worked for me.


    There are three types of people in this world.
    Those who can count and those who can't.

  • I understand how to get the custom formatting to work. I can use numbers or letters, as long as the references are unique.

    What I am trying to achieve is automation of the procedure so that by running a macro the number format of a specific column is changed. Ideally I would like the macro to refer to text within the workbook.

  • Try this in your code:

    Sub Macro1()
      Dim fmat As String
      fmat = Range("A1").Value
      Range("B:B").NumberFormat = Chr(34) & fmat & Chr(34) & "@"
    End Sub

    If A1 contains the string Q1/03- then that format will be applied to column B with whatever text is already in B coming after the -. If you have numeric data in Col B you might have to change it to "text" format first or change the @ symbol in the macro to a #.

    The CHR(34)'s are quote marks - needed because you have a zero as part of the format string - without the quotes, the zero in the "03" part of your string becomes a format place holder and any digit you type in the cell is placed in that position of the format string.

    (Thanks Neale, for reminding me about the @ in the formatting...don't know where my mind was this morning :wink1: )


  • rem1224,

    Your are an absolute star! The code was spot on.

    Thanks for your help.

    c_butta :biggrin:


    You'll have to forgive me but I've got a bit of a problem with the code. I've tried to play around with it a bit but I've only just started my journey down the VB road.

    The code works fine but I need the macro to run from the first sheet in my workbook with reference to a cell on the same sheet and change change the format of range B:B on the second sheet.

    Also I need the format of Range 2:2 on thirteen sheets following the second sheet to have the same format. There is fourteenth Sheet which I don't want to change any formatting on.

    Is all of this possible?

    Any help would really be appreciated...

    c_butta :confused:

  • Hi c_butta,

    You can mod the code to add references to the particular worksheet where the value comes from and the worksheet and range you want to format - - something like - -

    Sub Macro1()
      Dim fmat As String
      fmat = Worksheets("Sheet1").Range("A1").Value
      Worksheets("Sheet2").Range("B:B").NumberFormat = Chr(34) & fmat & Chr(34) & "@"
    End Sub

    You can use as many "NumberFormat" lines as you need with different worksheets/ranges to accomplish what you want.


  • Ralph,

    Thanks for this. It's really great.

    However I have a dilemma. The thirteen sheet names I referred to in my previous post will change every thirteen weeks (they refer to dates). From my understanding unlike in formulas, references to sheet names in macros don't change when the sheet names change. I am trying to avoid the need to edit the macro everytime the sheet names change.

    Is there any way that codes can refer to a range of sheets without actually refering to their names? (E.g, the third to fiftheenth sheets in the workbook)

    I'm guessing this might be a bit of a tall order.

    Thanks again for all your help.

  • Excel has you covered - -
    You can refer to the worksheets by their index number instead of the name - that way if the name changes, the macro will still work.

    Sub Macro1()
      Dim fmat As String
      fmat = Worksheets(1).Range("A1").Value
      Worksheets(2).Range("B:B").NumberFormat = Chr(34) & fmat & Chr(34) & "@"
    End Sub

    Worksheets(1) refers to the leftmost worksheet tab, Worksheet(2) the 2nd from the left, etc... As long as you don't rearrange the order of the worksheets the macro will give the same results regardless of how many times you change the name on the tab.


  • Ralph,

    Well what can I say. You are truly great!

    You have not only saved me a lot of heartache and pain you have taught me a great deal.

    Ozgrid is fantastic. My excel and VBA knowledge has really grown since discovering it. :clap2:

    Thanks again,


Participate now!

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