Change currency on all sheets based on one cell value

[Johanna Gronlund]

Hello,

I need to change currency formatting based on one selected value. So, if the
user changes Italy as their base country, I want the currency to change to €.
If they choose the UK, I want the currency to change to £. I have a cell
(Inputs!A1) which updates to 1 for euro country and 2 for the UK. The cells
ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and
Calculations!A15.

Is this doable?

Many thinks in advance.

 

[Matt Richardson]

Hello,

I need to change currency formatting based on one selected value. So, if the
user changes Italy as their base country, I want the currency to change to €.
If they choose the UK, I want the currency to change to £. I have a cell
(Inputs!A1) which updates to 1 for euro country and 2 for the UK. The cells
ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and
Calculations!A15.

Is this doable?  

Many thinks in advance.

Hi

You can use this code (where A1 is the cell which changes from 1 to 2
etc):-

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$A$1" Then
If Cells.Range("A1").Value = 1 Then
Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat = "[$
€-2]#,##0.00"
Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat = "[$
€-2]#,##0.00"
Worksheets("Calculations").Cells.Range("A15").NumberFormat =
"[$€-2]#,##0.00"
Else
If Cells.Range("A1").Value = 2 Then
Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat =
"£#,##0.00"
Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat =
"£#,##0.00"

Worksheets("Calculations").Cells.Range("A15").NumberFormat =
"£#,##0.00"
End If
End If
End If

End Sub


Hope this helps in some way.
KR Matt
http://teachr.blogspot.com

 

[Johanna Gronlund]

For some reason nothing happens if I paste this in the module 1. Is that the
right place to paste it? Is there anything else that I might be could be
doing wrong?

Thanks!

--
Johanna

Hello,

I need to change currency formatting based on one selected value. So, if the
user changes Italy as their base country, I want the currency to change to €.
If they choose the UK, I want the currency to change to £. I have a cell
(Inputs!A1) which updates to 1 for euro country and 2 for the UK. The cells
ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and
Calculations!A15.

Is this doable?

Many thinks in advance.

Hi

You can use this code (where A1 is the cell which changes from 1 to 2
etc):-

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$A$1" Then
If Cells.Range("A1").Value = 1 Then
Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat = "[$
€-2]#,##0.00"
Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat = "[$
€-2]#,##0.00"
Worksheets("Calculations").Cells.Range("A15").NumberFormat =
"[$€-2]#,##0.00"
Else
If Cells.Range("A1").Value = 2 Then
Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat =
"£#,##0.00"
Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat =
"£#,##0.00"

Worksheets("Calculations").Cells.Range("A15").NumberFormat =
"£#,##0.00"
End If
End If
End If

End Sub


Hope this helps in some way.
KR Matt
http://teachr.blogspot.com
.

 

[Matt Richardson]

For some reason nothing happens if I paste this in the module 1. Is that the
right place to paste it? Is there anything else that I might be could be
doing wrong?

Thanks!

--
Johanna

You can use this code (where A1 is the cell which changes from 1 to 2
etc):-

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
    If Cells.Range("A1").Value = 1 Then
        Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat = "[$
€-2]#,##0.00"
        Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat = "[$
€-2]#,##0.00"
        Worksheets("Calculations").Cells.Range("A15").NumberFormat =
"[$€-2]#,##0.00"
    Else
        If Cells.Range("A1").Value = 2 Then
            Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat =
"£#,##0.00"
            Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat =
"£#,##0.00"

Worksheets("Calculations").Cells.Range("A15").NumberFormat =
"£#,##0.00"
        End If
    End If
End If

End Sub

Hope this helps in some way.
KR Matt
http://teachr.blogspot.com
.

Paste this in the module of the worksheet that you want to change. It
should work then.

Matt
http://teachr.blogspot.com