CODEWITHSUNDEEP
excelvbanumber-formatting
KGx
KGx

Reputation: 15

How can I apply dynamic number formatting?

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 .

Upvotes: 1

Views: 278

Answers (2)

AsUsual
AsUsual

Reputation: 524

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

Upvotes: 1

neelsg
neelsg

Reputation: 4842

No need to use VBA. You can do this with conditional formatting.

To do the specific example:

  1. Go to cell B5
  2. Click Conditional Formatting > New Rule...
  3. Select Use a formula to determine which cells to format
  4. As the formula, put =$A$5="XRP"
  5. Click Format and on the Number tab, set it to custom such as #.##0,00000000 [$XRP]

I will leave it up to you to generalize this

Upvotes: 0

Related Questions