Replacing Custom Formats

[awseman]

Hi there,

is there a way to edit and then re-apply a custom format across a workbook?

For example, I have a uniform format of 0;[Red](0) for all my whole numbers but other custom formats (eg. 0%;[Red](0%) in other cells. I would like to remove the red colour for negative numbers.

I know I can edit the custom format manually but I have to do this cell by cell (or at least sheet by sheet) to affect all occurences. It's quite a big workbook and this would be a bit time consuming and very boring.

It seems like something that should be easy to do but I bet it's not!

cheers

Shane

 

[mikerickson]

You can click the little box to the upper left of the sheet, selecting all the cells on the sheet and format the cells en masse.

 

[Richard Schollar]

For the future Shane you might like to look into using Styles to format your sheet - if you want to change any particular style in the workbook (and have it cascade to all cells in the workbook with that style), you only have to change it the once, centrally.

 

[awseman]

thanks, Mike... I thought you might say that but I have amended the post to say that there is more than one custom format involved. Cheers!

great advice, Richard... where would I be without hindsight! In my defence, it's an old book that precedes my time here.

Thanks for your help!

 

[wsjackman]

if you have 2007, you can select by format.

if you don't you can probably write a macro to do th eformat change for you,

 

[Richard Schollar]

You can choose to Find by format in xl2002 and 2003 additionally so it looks like this could be an option for you. If you select Find All it will find all cells on the given sheet and you can then change the formats of just these cells - if you use Ctrl+A in the Find All box once found, you can select all the relevant cells and then it is a simple mattter of going Format>Cells>Number tab and choosing a different number format

 

[awseman]

I have 2002 and have used the 'Find by Format' function and that works really well... thanks very much, Richard!

and you too, Mr Jackman... thanks a lot for you help!