Excel 2007 - Conditional Currency format

Question

Hi there,

Is it possible to set the currency of a column (£ / € / $) dependent on the value of a cell?

Eg, If i determine that the address im using in the workbook is America the workbooks currency will be set to dollars.

This would save the time of having multiple spreadsheets for different currencies.

Thanks in advance to anyone who can help.

Noel

Answer 1

Hi,

Anyone who may be interested I came up with the following, if there is a better way of figuring this out please tell

'
'   Set the Currency of the worksheet
'
Public Sub setCurrency()

Dim eurFormat As String
Dim strFormat As String
' Euro currency format
eurFormat = "[$€-1809]#,##0.00"
' Sterling currency format
strFormat = "[$£-809]#,##0.00"


If Worksheets("ws-with-cell-value").Cells(1, 2).Value = "GBP £" Then
    Worksheets("ws1").Columns("C:C").NumberFormat = strFormat
    Worksheets("ws2").Columns("I:J").NumberFormat = strFormat
End If

If Worksheets("ws-with-cell-value").Cells(1, 2).Value = "EUR €" Then
    Worksheets("w1").Columns("C:C").NumberFormat = eurFormat
    Worksheets("ws2").Columns("I:J").NumberFormat = eurFormat
End If

End Sub

Then called this sub routine on workbook_activate() and Sub Worksheet_Change for the "ws-with-cell-value"

Hope this might help someone out there