Conditional Formatting Problem with currency.

[Summerstone]

Hi all,

I have a quotation tool where cell C13 can be "GBP", "USD" or "EUR", a
data validation where the user can choose the currency.

I'd like the figures in the spreadsheet to have their currency format
dependant on that cell.

Conditional formatting isn't an option, this only allows basic
formatting, not currency.

I've tried looking at a selection change event in VBA but with my
limited experience it going nowhere.

Any suggestions would be much appreciated!!

Many thanks,
Tony Summers

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Offset(0, -1).Value
Case "GBP": .NumberFormat = "£#,##0.00;(£#,##0.00)"
Case "USD": .NumberFormat = "\$#,##0.00;(\$#,##0.00)"
Case "EUR": .NumberFormat = "#,##0.00?;(#,##0.00?)"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Summerstone]

Hi Bob,

Many many thanks for your quick reply, much appreciated.

It's not your fault, but I'm struggling to make the code work. I
pasted it in as an event code but must be missing something simple.

A couple of queries:
The WS_RANGE... is that the area I wish to be formatted? (I notice
you've commented "change to suit").
Where is the user input of "GBP, USD" identified by the code?
I assume a "worksheet_change" event is simply that? A user "changes"
something?

Sorry to be slow, I'm very much at the start of my VBA journey!

Many thanks,
Tony

 

[Summerstone]

I've managed to throw together the following which seems to be working
fine.
I can now enter the currency in A1 and the formatting of the quotes
changes accordingly.

I'm still concious that this is a bit clunky, so am still keen to
understand Bob's code above, if anyone is willing to humour me.

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("A1") = "USD" Then
Range("G4:H33").Select
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("G4").Select
Range("a1").Select
Else
If Range("A1") = "GBP" Then
Range("G4:H33").Select
Selection.NumberFormat = "£#,##0.00"
Range("A1").Select

Else
If Range("A1") = "EUR" Then
Range("G4:H22").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("A1").Select

End If
End If
End If

End Sub

Best regards,
Tony

 

[Summerstone]

I've managed to throw together the following which seems to be working
fine.
I can now enter the currency in A1 and the formatting of the quotes
changes accordingly.

I'm still concious that this is a bit clunky, so am still keen to
understand Bob's code above, if anyone is willing to humour me.

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("A1") = "USD" Then
Range("G4:H33").Select
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("G4").Select
Range("a1").Select
Else
If Range("A1") = "GBP" Then
Range("G4:H33").Select
Selection.NumberFormat = "£#,##0.00"
Range("A1").Select

Else
If Range("A1") = "EUR" Then
Range("G4:H22").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("A1").Select

End If
End If
End If

End Sub

Best regards,
Tony

 

[Bob Phillips]

My code adapted to your situation should be

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G4:H33"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Select Case .Value
Case "GBP": Me.Range(WS_RANGE).NumberFormat = "£#,##0.00"
Case "USD": Me.Range(WS_RANGE)..NumberFormat =
"[$$-409]#,##0.00"
Case "EUR": Me.Range(WS_RANGE)..NumberFormat = "[$?-2]
#,##0.00"

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

WS_RANGE would normally point to the range being changed, but here I have
altered it to the range affected down the line, and test A1 for a change. If
A1 changes, I reformat the WS_RANGE range accordingly.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I've managed to throw together the following which seems to be working
fine.
I can now enter the currency in A1 and the formatting of the quotes
changes accordingly.

I'm still concious that this is a bit clunky, so am still keen to
understand Bob's code above, if anyone is willing to humour me.

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("A1") = "USD" Then
Range("G4:H33").Select
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("G4").Select
Range("a1").Select
Else
If Range("A1") = "GBP" Then
Range("G4:H33").Select
Selection.NumberFormat = "£#,##0.00"
Range("A1").Select

Else
If Range("A1") = "EUR" Then
Range("G4:H22").Select
Selection.NumberFormat = "[$?-2] #,##0.00"
Range("A1").Select

End If
End If
End If

End Sub

Best regards,
Tony

 

[Summerstone]

Works perfectly, really appreciate your help Bob.

As importantly, I understand the approach too!

Many thanks,
Tony