format cells range

  • Thread starter Thread starter diacci1st
  • Start date Start date
D

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
 
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)
 
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
 
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
 
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
 
Assuming A1 of sheet with range to format has a formula like =menusheet!F12

Change the event type to Private Sub Worksheet_Calculate()


Gord
 
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
 
Private Sub Worksheet_Calculate() 'only

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


Gord
 
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
 
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
 
Back
Top