Conditional Formatting script

[Guest]

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.

 

[Tom Ogilvy]

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

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.

 

[Guest]

Are you going to use the Ax and Bx figures just for show (ie for printing out and therefore they can be a string rather than a value) or do the then have to interact with other figures to produce totals etc?

 

[Tom Ogilvy]

Had a typo in the original - see corrected:

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