Using VBA Code to apply Currency Format?

[mynamesjason]

Here's the scenario in Excel 2003:

I have a list validation in cell B86 to force the user to either choose USD or JPY. What I would like to happen is when the user chooses a currency, it will apply a Currency format to have 0 decimal places and the appropriate currency symbol.

Any ideas?

Thanks.

 

[HalfAce]

You didn't mention where you want to format so this example just formats A86 whenever the value in B86 changes. You can change that to format whatever you want.

Right click the sheet tab and choose View Code. Paste this in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$86" Then Exit Sub
Select Case Target.Value
  Case "USD"
    Range("A86").NumberFormat = "$#,##0"
  Case "JPY"
    Range("A86").NumberFormat = "[$¥-2] #,##0"
End Select
End Sub

Hit Alt+Q to close the vb editor, enter a numeric value in A86 and try a change from your validation list in B86.

Hope it helps.

 

[rs2k]

See if this works for you;

Sub YEN()
 
    Range("B86").Select
    Selection.NumberFormat = "[$¥] #,##0"
 
End Sub
 
Sub USD()
 
    Range("B86").Select
    Selection.NumberFormat = "[$$-409]#,##0"
 
End Sub

HTH
Colin