Right click on the sheet tab and Select view code

there are two dropdowns at the top of the resulting sheet module
in the left dropdown at the top, select Worksheet
in the right dropdown at the top, select Calculate

This will put in a declaration for the calculate event which fires whenever
the sheet calculates:
Private Sub Worksheet_Calculate()

End sub

put code similar to the below in that event.



Private Sub Worksheet_Calculate()
dim cell as range
for each cell in Range("C1:C30")
if lcase(cell.value) = "p" then
cells(cell.row,1).Resize(,2).Numberformat:="$ #,##0.00"
elseif lcase(cell.value) = "q" then
cells(cell.row,1).Resize(,2).NumberFormat:="£ #,##0.00"
end if
Next

End Sub

--
Regards,
Tom Ogilvy

"Andy" wrote in message
...
Hi,

I'm trying to find a way to change the currency symbol in a cell based on
a value in another cell, but as usual Excel doesn't provide an easy way to
do this.

My first thought was to use conditional formatting and a formula, but
conditional formatting doesn't allow the currency symbol to be changed, so
the only option i have now is to use a small macro to do this.
Unfortunately i do not have much experience with macro programming, so i'm
hoping someone here can help.

What i would like to do is specifiy a range of cells in the macro (say
c1:c30) and then have the macro search through this range one cell at a
time. Anywhere it finds the value P it sould change the currency symbol in
cells Ax and Bx to a certain symbol. If it finds the value Q it should
change Ax and Bx to another currency symbol.

That's it. Sounds easy, but i've tried loads of things and come up blank.

Any help would be appreciated.

Cheers, and Merry Xmas

Andy.