macro to generate a list of cells using a specific custom cell style

Hi, I'm looking for some VBA code that might not be possible but I wanted to get some opinions anyway.

I have a workbook with many worksheets that contain numerous cells with a specific custom cell style called "topic". I would like to be able to generate a master list of the cells using this custom style on a separate wkst (new if not already present). Ideally, I would like the list to contain the contents of the cells (e.g. "Topic1","Topic2", etc.) as well as a reference and hyperlink to that cell. I would be fairly certain these things can be done with built-in styles, I'm just not sure about custom cell styles. Thoughts?

Below is a code I found that works in a similar vain - it lists all the sheet names onto a specific wkst and provides a hyperlink to each one. I would like pretty much the same thing except for each cell using the custom style. Maybe this would serve as a starting point? I'd appreciate any suggestions

Please Login or Register  to view this content.

Perhaps a macro like this could work?

Please Login or Register  to view this content.

This macro writes the result to Sheet1 i.e. the sheet name where the specific font is found, the cell address i.e. $B$4 and the value of that cell. So the problem is if this specific font you are using is listed in the "Drop down" for fonts. You must replace font name "Arial" in the macro of cource.

Be warned that this is a "slow" macro as it checks all cells in the used range of all sheets except sheet1. If the the specific font is used as say headings or in certain columns the search area for the font could be restricted and this will speed up the time it takes to search all sheets.

The hyperlink part could be added assuming this macro works.

Alf

Hello,

Give this try. It will list all your cell styles (except "Normal") like Alf already said watch out it might take some time.

I did some testing and this is the speeds I get.

cells: 76341
Time 52.91s

Cells: 28882
Time: 10.39s

Run the main procedure GetCellStyles

Please Login or Register  to view this content.

Thanks