CODEWITHSUNDEEP
excelcurrencyvba
tbowden
tbowden

Reputation: 1058

Currency in Excel

I would like to change the currency symbol in a cell depending on the value in another cell, in the row.

For example if cell A1 contains "EU" then cell A2 will be "€ 5.00" (If the calculation was 3+2), and if the content in cell A1 were to be changed to "GB" then £ 5.00 will display in A2.

Is is possible to do this in VBA? I would prefer NOT to do a simple IF statement.

Upvotes: 0

Views: 438

Answers (3)

rwking
rwking

Reputation: 1032

You most certainly can! Utilize conditional formatting on your range with the currency value. Here is how I accomplished this in a test...

  1. Select the first cell in your "amount" range and then click Conditional Formatting -> New Rule.

  2. Select "Use a formula to determine which cells to format"

  3. In the box, type =if([Cell w/ Country] = "USA", 1, 0 ). Note, [Cell w/ Country] is whatever cell that indicates the type of currency. In my example, the formula is =if($A6="USA", 1, 0). [Pay close attention to the cell reference $A6 - you want the formula to work for the entire column (in this example), so don't enter $A$6.]

  4. After typing the formula, click the "Format" button.

  5. Click on the "Number" tab and choose "Currency" in the Category list

  6. Using the Symbol dropdown, select the symbol corresponding to the rule you just created - in this case, $, then click OK.

  7. Finally, you're left with the Rules Manager window. You'll want to select the range that the rule will apply to. Enter the range, or click the range selector button and highlight the range. Then, click OK.

Here are some screenshots that might be helpful:

enter image description here

enter image description here

enter image description here

Swap around the Country, and the symbol changes.

enter image description here

Upvotes: 4

BruceWayne
BruceWayne

Reputation: 23283

You don't need VBA for this - just good 'ol conditional formatting. With your A2 cell selected, go to Home --> Conditional Formatting. Then click "New Rule" --> "Use a Formula to determine which cells to format".

For the GBP formatting, use the formula =$A$1="GB", then click "Format", go to "Number" along the top, choose "Currency", then just find the GBP symbol. Click "OK" all the way out. There you go!

For the EUR part, repeat the above, but instead use =$A$1="EU" and find EUR symbol in Currency.

Upvotes: 0

Balinti
Balinti

Reputation: 1534

If it only for one cell then a very short macro will do it. just change for custom format as within the code: Sub Macro2()

   If Range("a1").Value = "EU" Then Range("A2").Select: Selection.NumberFormat = """€""#,##0"
If Range("a1").Value = "GB" Then Range("A2").Select: Selection.NumberFormat = """£""#,##0"


End Sub

Upvotes: 0

Related Questions