Conditional Formatting script
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. |
Conditional Formatting script
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. |
Conditional Formatting script
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?
|
Conditional Formatting script
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 -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... 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. |
All times are GMT +1. The time now is 02:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com