format cells range

[diacci1st]

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

 

[Teethless mama]

Select your range > condiditional Formatting
Condition1: =$A$1="GBP"
Format as > Number > Currency > Symbol: select Eng (US)

Condition2: =$A$1="USD"
Format as > Number > Currency > Symbol: select Eng (Uk)

 

[diacci1st]

Thank you!
unfortunattly I only get the possibility to format FONT/BORDER/PATTERN in
the conditional formatting.. am I doing something wrong?

Thank you
AD

 

[Gord Dibben]

Regular CF won't handle that type of formatting.

Event code could do the trick.

Adjust to suit. DV dropdown for selection 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("B10:B13")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.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

 

[diacci1st]

Thanks that is great would it be possible to have the same code as sheet
activate as the selection of the currency is done on a menu page in an other
sheet. but I have just a reference cell on the sheet where I need the change
of formatting
Thank you
AD

 

[Gord Dibben]

Assuming A1 of sheet with range to format has a formula like =menusheet!F12

Change the event type to Private Sub Worksheet_Calculate()


Gord

 

[diacci1st]

Hi
I did is the code that I have inserted but I get an error message "procedure
decleration does not meet the description"
Private Sub Worksheet_Calculate(ByVal Target As Range)
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Private Sub Worksheet_Calculate() 'only

drop this bit........ByVal Target As Range


Gord

 

[diacci1st]

Hi
If I do that I get an error on the the below line for "object required"
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub

 

[Gord Dibben]

Yes......you would<g> My slip.

Try this version which assumes J2 has a linking formula to a changing cell
on another sheet.

I also added the UCase to make the value in J2 case-insensitive.

i.e. usd or uSd or USd would be OK

Private Sub Worksheet_Calculate()
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case UCase(Me.Range("J2").Value)
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

Gord