You could do this using sheet event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value

Case "HKD"
Target.Offset(0, -1).NumberFormat = "[$HKD] #,##0.00"

Case "GBP"
Target.Offset(0, -1).NumberFormat = "[$GBP] #,##0.00"

Case "Undefined"
Target.Offset(0, -1).NumberFormat = "#,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

Add other currencies and cases to suit.


Gord Dibben MS Excel MVP

On Mon, 19 Nov 2007 11:18:01 -0800, Vinod
wrote:

I mean to say, I wanted to use conditional format for a cell which is based
on a cell A1.

In other wards in my workbook cell B1 consists value 100 and there is a
combobox (Formcontrol) which is linked to cell A1. Dropdown consists of
currencies list like USD,CNY,GBP,HKD, UNDEFINED(No symbol). If I select "CNY"
from dropdown then "CNY" will store at cell A1.

1. Finally I wanted to display cell B1 value with prifix of cell A1 value
i.e., B1 should display as CNY 100.

2. If I select "GBP" then B1 should display as GBP 100.
3. If I select "HKD" then B1 should display as HKD 100.
4. If I select "UNDEFINED" then B1 should display simplay as 100 (without
symbol).

Advanced Thanks,
Vinod