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