Search code examples
excelformats

Custom Excel Formats that reference cells

I would like to have an Excel sheet that displays a custom format. The custom format uses the contents of another cell. Here is an example:

Column A : Show a column of numbers in accounting format in the currency of cell(b1).

Cell(B1) : "XYZ"

The intention is that users can enter their own currency. I know there are formatting tools to do this in Excel but this is a question that implements a custom format based upon another cell contents. That's the real question...

Solution

  • Assuming you want something like this:

    enter image description here

    enter image description here

    ..then just put this in the Sheet Module:

    Sub worksheet_Change(ByVal Target As Range)
Dim sFormat As String

If Not Intersect(Target, Range("NumberFormat")) Is Nothing Then
    sFormat = Chr(34) & Target.Value & Chr(34) & " "
    Range("FormatWhat").NumberFormat = sFormat & "$#,##0;" & sFormat & "[Red]-$#,##0;-"
End If

End Sub

    ...and give B1 the Name NumberFormat in the Name Box:

    enter image description here

    ...and likewise name some or all of column A "FormatWhat".

    (Using Named Ranges avoids hard-coding references in your code. If you hard code cell address into your code, those references will be pointing at the wrong place if you (or a user) later adds new rows/columns above/to the left of those hard-coded references. Using Names avoids this, and makes for more robust code.

    I almost never hard-code cell addresses in my code. I almost always use Excel Tables aka ListObjects to hold any data that VBA interacts with for the same reason...ListObjects are dynamic named ranges that Excel automatically expands/contracts to suit the data.)