Currency Formatting

[Aaron]

I have a worksheet in which I would like to change the
currency format of one cell (ie A1) based on the data in
another cell (A2). I would like A1 to change from USD to
Euro, for example (I have about 20 currencies that I work
with). How do I do this?

Thanks

 

[Vasant Nanavati]

Hi Aaron:

You would probably need to use a macro for this. Try something like this in
the code module for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Target.Value
Case "Germany"
Range("A1").NumberFormat = "#,##0 [$DM-407];-#,##0
[$DM-407]"
Case "Denmark"
Range("A1").NumberFormat = "[$kr-406] #,##0_);([$kr-406]
#,##0)
'..........etc.
End Select
End If
End Sub

It's pretty messy, but I can't think of a better way.

Regards,

Vasant.