CODEWITHSUNDEEP
excelexcel-2007
noelmcg
noelmcg

Reputation: 1067

Excel 2007 - Conditional Currency format

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

Upvotes: 1

Views: 597

Answers (1)

noelmcg
noelmcg

Reputation: 1067

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

Upvotes: 1

Related Questions