How can I apply dynamic number formatting?

Question

I've programmed an Excel document for my cryptocurrency portfolio. I add new currencies in a table, to which I pull data via an API to get the prices. I format the result like this: #.##0,00000000 [$ETH] (for Ethereum).

What I would like to achieve is, that when I input currency in column A, the value in column B is formatted to this currency.

Example: http://prntscr.com/p3sof8

So if I input XRP in A5, the value in B5 would read 0,00000000 XRP. There's no fixed number of currencies I'd use, since I may add new every day.

Basic version of the above logic would be:

Input currency from column A into formatting argument like: #.##0,00000000 [$], except for BTC where the format would be: ฿0,00000000 .

Answer 1

Place this in your Sheet e.g "Sheet1"

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count = 1 Then
            If .Column = 1 Then
                If .Value <> "BTC" Then
                    .Offset(, 1).NumberFormat = "0.00000000" & """ " & .Value & """"
                Else
                    .Offset(, 1).NumberFormat = """" & ChrW(&HE3F) & """" & "0.00000000"
                End If
            End If
        End If
    End With
End Sub