Conditionally formatting currency
 

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

Conditionally formatting currency
 

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" wrote in message
...
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

Conditionally formatting currency
 

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" wrote:

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" wrote in message
...
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

Conditionally formatting currency
 

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



"Yendorian" wrote in message
...
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" wrote:

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" wrote in message
...
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