Linking the currency format of a range to another cell?

[yorkshirelad]

I have a range of cells D1:D23 that need to display prices in Euros or Sterling.

The user can select a currency from a drop-down in cell C4, this then populates the cells D1:D23 with the correct data

Is there a way I can automatically change the currency format of this range from £'s to Euros depending on the cell contents of C4.

I've had a few goes at setting up conditional formatting with a formula, but the results aren't consistent.

Many thanks for any assistance

 

[Weaver]

IFAIK, you can't change numberformats with CF.

Right click the sheet tab, select view code and insert the following code. You'll need to change the range reference E1:E100 to suit.

Having said that, I'm using xl2002, so maybe later versions are more flexible.

 Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$4" Then
        Application.EnableEvents = False
        With Range("E1:E100")
            Select Case Target.Value
                Case "Euro"
                    .NumberFormat = "#,##0.00 [$€-1]"
                Case "Sterling"
                    .NumberFormat = "£#,##0.00"
            End Select
        End With
        Application.EnableEvents = True
    End If
End Sub

I used select..case so you can include more than 2 options easily

 

[p45cal]

How is "this then populates the cells D1:D23 with the correct data" done?
Is a VBA solution wanted? (It may have to be)
What sort of things are in the dropdown, and is it a Data Validation dropdown or something else?

It could be something in the sheet's change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
    With Range("D1:D23")
        If Range("C4").Value = "Euro" Then
            .NumberFormat = "[$€-2] #,##0.00"
        Else
            .NumberFormat = "$#,##0.00"
        End If
    End With
End If
End Sub

 

[yorkshirelad]

IFAIK, you can't change numberformats with CF.

Right click the sheet tab, select view code and insert the following code. You'll need to change the range reference E1:E100 to suit.

Having said that, I'm using xl2002, so maybe later versions are more flexible.

 Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$4" Then
        Application.EnableEvents = False
        With Range("E1:E100")
            Select Case Target.Value
                Case "Euro"
                    .NumberFormat = "#,##0.00 [$€-1]"
                Case "Sterling"
                    .NumberFormat = "£#,##0.00"
            End Select
        End With
        Application.EnableEvents = True
    End If
End Sub

I used select..case so you can include more than 2 options easily

Many thanks Weaver - sorry for the delay in posting a reply I've been away for the weekend. That's great.

 

[yorkshirelad]

How is "this then populates the cells D1:D23 with the correct data" done?
Is a VBA solution wanted? (It may have to be)
What sort of things are in the dropdown, and is it a Data Validation dropdown or something else?

It could be something in the sheet's change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
    With Range("D1:D23")
        If Range("C4").Value = "Euro" Then
            .NumberFormat = "[$€-2] #,##0.00"
        Else
            .NumberFormat = "$#,##0.00"
        End If
    End With
End If
End Sub

Many thanks p45cal - sorry for the delay in replying I've been away for the weekend. That's really appreciated