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" wrote:

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

Change the event type to Private Sub Worksheet_Calculate()


Gord

On Tue, 30 Sep 2008 10:16:08 -0700, diacci1st
wrote:

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" wrote:

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

On Tue, 30 Sep 2008 08:31:02 -0700, diacci1st
wrote:

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