Let say rather than put a single character in B1 via VLOOKUP, we want to
format a value in B1 based upon the entry in A1.

Unfortunately this cannot be accomplished easily with conditional
formatting. Insert the following macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A")
Set t = Target
If Intersect(t, r) Is Nothing Then Exit Sub
v = t.Value
With t.Offset(0, 1)
Select Case v
Case "dollar"
.NumberFormat = "$#,##0.00"
Case "pound"
.NumberFormat = "[$£-809]#,##0.00"
Case "euro"
.NumberFormat = "[$‚¬-2] #,##0.00"
End Select
End With
End Sub

If you enter {pound, dollar, euro} in any cell in column A, the macro will
automatically re-format the adjacent cell in column B.

REMEMBER the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200756


"Eqa" wrote:

Gary I have just realised that I cannot enter a numeric value into the cell
without ruining the VLookup formula. How do I get around this?

Thanks,
Eqa

"Gary''s Student" wrote:

Use a combination of Data Validation and VLOOKUP().

Somewhere in the worksheet create a table. The first column of the table is
a textual description of the currency:

Dollar
Euro
Mark
.
.
.
Next to the first column, insert a single character for each currency symbol

In A1 put the data validation pull-down
In B1 put the VLOOKUP to get the symbol.
--
Gary''s Student - gsnu200756


"Eqa" wrote:

I have a cell say A1 with a drop down list of choosen currencies.eg. US $. In
the next cell say B1, I want it to insert the correct symbol depending on the
currency picked to be automatically inserted. In this case $. How can I do
this?

Hope this is clear.

Thanks Eqa.