Custom Excel Formats that reference cells

Question

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...

Answer 1

Assuming you want something like this:

..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:

...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.)

Answer 2

A worksheet_change in the worksheet's private code sheet can alter the number formatting in column A.

private sub worksheet_change (byval target as range)
    if not intersect(target, range("b1")) is nothing then
        if len(cells(1, "b").value2) = 3 then
            dim f as string
            f = ucase(cells(1, "b").value2)
            f = "0 \" & left(f, 1) & "\" & mid(f, 2, 1) & "\" & right(f, 1)
            range(cells(2, "a"), cells(rows.count, "a").end(xlup)).numberformat = f
        end if
    end if
end sub

There are a maximum number of custom number formats that can be added to the existing number formats without deleting previously created cnfs; I think it's around 30 or so.