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

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

But I wanted this feature without VBA code. And more over I wanted this
feature through Conditional Format only. But Conditional format doesn't have
this feature i.e, it is not possible to give curreny formats at conditional
format. So, is there any other solution.

Regards,
Vinod

"Gord Dibben" wrote:

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

Adding varaible text is not a format. There are really only a few choices.
1. Go with Gord's solution
2. Add a Cell C1 that is the concatenation of A1 and B1.

Conditional formatting will not work and regular formatting can not access
values in other cells...
--
HTH...

Jim Thomlinson


"Vinod" wrote:

But I wanted this feature without VBA code. And more over I wanted this
feature through Conditional Format only. But Conditional format doesn't have
this feature i.e, it is not possible to give curreny formats at conditional
format. So, is there any other solution.

Regards,
Vinod

"Gord Dibben" wrote:

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

I know it is not possible through CF so I posted the code.

I don't know of any other way except use a third cell to combine A1 and the
choice from B1

in C1 =B1 & " " & A1


Gord

On Mon, 19 Nov 2007 12:07:00 -0800, Vinod
wrote:

But I wanted this feature without VBA code. And more over I wanted this
feature through Conditional Format only. But Conditional format doesn't have
this feature i.e, it is not possible to give curreny formats at conditional
format. So, is there any other solution.

Regards,
Vinod

"Gord Dibben" wrote:

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