Conditionally formatting currency

[Yendorian]

In Excel 2003 Pro, is there any way of formatting currency in number format
conditionally - and how is it done?
For example, cell A1 can be « $ », « € », or « £ ».
If cell A1 is « $ », then the contents of cell B1 should be formatted with a
$ sign whereas if A1 = « € », then the contents of cell B1 should be
formatted with the € sign and so on.
Thanks for any help

 

[Bernie Deitrick]

Yendorian,

You can use the worksheet's change event. Copy the code below, right-click the sheet tab, select
"View Code", and paste the code into the window that appears. This assusmes that your cell A1
doesn't have the << and >> but just the currency symbols. Otherwise, you would need to clean up
the value from cell A1.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("B1").NumberFormat = Target.Value & "0.00"
End Sub

 

[Yendorian]

Thanks for that. It works perfectly with the £ and € sign but returns the €
sign again when it should be the $. Could this be because my computer is set
up with € as the main currency?

 

[Bernie Deitrick]

Try finding a currency format that works for dollars, then record a macro to
get that string, and use it in the change event like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Target.Value = "$" Then
Range("B1").NumberFormat = FORMAT STRING THAT WORKED HERE
Else
Range("B1").NumberFormat = Target.Value & "0.00"
End IF
End Sub