Format Query

[jdpjtp910]

Is there a way to get a print out of all the cells format used in a workbook?

I have a workbook over about 35 worksheets. I need to create data integrity for the workbook. I first need an as-is analysis before I can start. What is the fastest way to get all cells formats (preferably printed or listed)??

Using Excel or VBA would be fine.

Thank you,
Josh

 

[ChrisM]

Are you sure going this route is necessary? If you have the workbook right now, then you already know the as-is state. It is exactly what you are looking at. Use sheet protection and lock all the formatting down.

To capture all the cell formatting could be a massive exercise. You would loop through all 35 sheets, then loop through every cell in each sheet, capture selected formatting descriptions, and write all that to an output sheet, one row per cell, and each row would have as many columns as formatting attributes you decide to track. Something like this:

SheetName, CellAddress, InteriorColor, Border, Font, FontSize, Alignment, etc, etc

Even assuming a tiny 10 rows and 10 columns used per sheet, you would be up to 3500 rows of data to capture all 35 sheets. I'd guess in reality you could end up with 50,000 to 500,000 rows of formatting data.

Are you sure this is what you want?

 

[jdpjtp910]

It is needed as the company is document heavy and there is no documentation for this workbook.

So basically loop through all worksheets (use ranges to control length) then search for cells with data and dump the data format into a new worksheet?

 

[ChrisM]

This is a pretty unconventional request, so there may be other ways, but that's the only way I could think to do it. The code itself is pretty easy, the only hard part would be trying to limit the analyzed range on each worksheet, you'd want to set the lower boundary row and the right-most boundary column for each sheet.

At first you mentioned tracking cell formatting, but now you just mentioned data formatting. Which specific formatting elements would you want to track? You may have to create some custom codes to track them.

 

[jdpjtp910]

I am just looking for format (number, currency, etc) and decimal spaces if applicable.

The reason the macro would be nice is this is the first (pilot) workbook to go through this process. There are over 100 more that need to be completed. This is needed for requirements document and supple specs.

 

[ChrisM]

activesheet.range("A1").numberformat

This will give you the format code for a cell, the same as if you were to right click the cell, choose Format, and read the text within the Type: box.