Conditional Formating of Numbers

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?
 
R

RagDyeR

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?
 
S

ShaneDevenshire

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.
 
G

Gord Dibben

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
 
Top