Examine and change format for each cell containing zero

[ou812]

Hi all,

I have about 30 spreadsheets I have to process every week. For each cell in these spreadsheet that contain a 0, I have to manually change the format to currency without the $ sign. If a cell has a value other than o, I need to leave it in the current format. As you can imagine, doing this manually is quite time consuming. Is there a script that can examine each cell and make the changes when needed?

 

[ou812]

The code DENomad posted works great if I run the module from inside the spreadsheet I am working with. If I save the same code (an exact cut and paste) in my personal workbook and then try to run it from the exact same spreadsheet, I get a Runtime error 9 "subscript out of range." Anyone know why this might be?

Edit: I had to change the ThisWorksheet.sheet(..) to ActiveSheet. Now it works.

 

[DENomad]

No worries...