Currency Marker in Conditional

[jim]

I know this may be stretch, but does anyone know of a way to display
different cell formatting in a conditional? I have a spreadsheet that
reports values in the currency of the local country. Only one country
is shown at a time, so sometimes the values are in Euros and other
times in US Dollars. Is it possible to change the cell formatting to
display the appropriate currency format based on a selection in a
different cell?

Example: If(A1="UK",*use pound symbol*,*otherwise use US dollar sign*)

Thanks for any insight.

 

[somethinglikeant]

Hi Jim,

you could use a macro:-

Imagine your marker is in column A and your value in column B.
press Alt+F11
right click in the upper left pane and choose Insert Module
paste this code
:=====================================

Sub FormatCurrency()

[A1].Select

Do Until IsEmpty(ActiveCell)

If ActiveCell.Value = "UK" Then _
ActiveCell.Offset(0, 1).NumberFormat = "[$£-809]#,##0.00"
If ActiveCell.Value = "US" Then _
ActiveCell.Offset(0, 1).NumberFormat = "$#,##0.00"

ActiveCell.Offset(1, 0).Select

Loop

End Sub

:========================================

http://www.excel-ant.co.uk