Format Range of Cells A Particular Currency Depending On Cell Value

Question

I've been trying to do IF statements and case statements to achieve this and nothing has been remotely successful.

Basically I have a 5 tab workbook that has different named ranges within each sheet that are meant to be a currency format.

However based on the value of a cell in the 1st tab (e.g. it could be CAD, USD, NOK, etc.) the currency format on those range of cells should change.

I.e. If the cell value in tab 1 is equal to CAD then all the named ranges in tabs 2-5 should be "$", if USD still "$", if NOK "kr " etc.

Any help would be greatly appreciated!

Answer 1

Record macro to get your currency format syntax and add to your IF statement ie..

Selection.NumberFormat = "[$kr-414] #,##0.00"

You also might consider using a Case Select instead of multiple Ifs

Answer 2

You could put this code in the code of the Sheet of Tab1. When the value of A1 Cell Changes, it triggers the tables of the sheets you need.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Range("A1").Value
        Case "CAD"
        Worksheets("Sheet2").Range("Table1").NumberFormat = "#,##0.00 [$CAD]"
        Worksheets("Sheet3").Range("Table2").NumberFormat = "#,##0.00 [$CAD]"
        Worksheets("Sheet4").Range("Table3").NumberFormat = "#,##0.00 [$CAD]"
    Case "USD"
        Worksheets("Sheet2").Range("Table1").NumberFormat = "#,##0.00 [$USD]"
        Worksheets("Sheet3").Range("Table2").NumberFormat = "#,##0.00 [$USD]"
        Worksheets("Sheet4").Range("Table3").NumberFormat = "#,##0.00 [$USD]"
    Case "NOK"
        Worksheets("Sheet2").Range("Table1").NumberFormat = "#,##0.00 [$NOK]"
        Worksheets("Sheet3").Range("Table2").NumberFormat = "#,##0.00 [$NOK]"
        Worksheets("Sheet4").Range("Table3").NumberFormat = "#,##0.00 [$NOK]"
    End Select

End Sub