Conditional Formating of Numbers

  • Thread starter Thread starter bostontj98
  • Start date Start date
B

bostontj98

I have a column indicating currency (validation list) and I would like
the values to the right of that to be fomated with the appropraite
Curr symbol but the Conditional Formatting tool doesn't offer any
number options.

Ideas?
 
What determines which currency symbol to use?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I have a column indicating currency (validation list) and I would like
the values to the right of that to be fomated with the appropraite
Curr symbol but the Conditional Formatting tool doesn't offer any
number options.

Ideas?
 
Hi,

That is true in 2003 but not in 2007. In 2007 you can do that.

I think in 2003 you will need to consider VBA.
 
You could employ worksheet event code.

Adjust to suit. DV dropdown assumed A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("B1:B100") 'adjust to suit
Select Case UCase(Target.Value)
Case "USD"
.NumberFormat = "$#,##0.00"
Case "Pound"
.NumberFormat = "£#,##0.00"
Case "Euro"
.NumberFormat = "€#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that module, edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP
 
Back
Top