HI Vinod,

I'm not sure if you're still monitoring your posting. I have a similar
situation which may work for yours as well. The way I setup is I put
the currency symbols in column A from A1 to A10 and the numbers in
column B. I also used Data-Validation on column A, so the user is
forced to select 5 types of currency symbols (CAD, USD, EUR, GBP and
Unassigned). The macro is done through a worksheet change event. Let
me know if this works for you.

Mike James
======

Private Sub Worksheet_Change(ByVal Target As Range)

Set t = Target

On Error GoTo ErrHandler:

If Intersect(t, Range("A2:A10")) Is Nothing Then Exit Sub

Select Case t
Case "CAD"
t.Offset(0, 1).NumberFormat = "[$CAD ]* #,##0"

Case "USD"
t.Offset(0, 1).NumberFormat = "[$USD ]* #,##0"

Case "EUR"
t.Offset(0, 1).NumberFormat = "[$EUR ]* #,##0"

Case "GBP"
t.Offset(0, 1).NumberFormat = "[$GBP ]* #,##0"

Case "Unassigned"
t.Offset(0, 1).NumberFormat = "[$ ]* #,##0"

End Select

Exit Sub

ErrHandler:

End Sub