PDA

View Full Version : Conditional Formats - printing a list of formats used



kualjo
08-17-2006, 01:23 PM
I have a number of worksheets that use varying kinds of Conditional Formatting. When creating a similar sheet in a different workbook, I need to be able to use the same CF's, but it's rather tedious and cumbersome to have to remember every little bit of it, and just as bad to bounce back and forth between workbooks, checking every cell to see what the formats are. Is there some code that I can write that will list or print the Conditional Formats of each cell?
(No two sheets are alike. All of them have the same columns, but they all have different row setups based on unique identifiers. There are also different summary rows with their own formats.)
All help is appreciated.

matthewspatrick
08-17-2006, 01:29 PM
What info exactly are you after? By the time you drill down into all the collections, objects, and properties involved in conditional formatting, you are talking about a couple or a few dozens of data elements...

kualjo
08-18-2006, 06:15 AM
Ideally, I want a listing of which cells have conditional formats, what the conditions are for each cell, and the format assigned. Something like this:

Cell Condition Format
A12 IF <0 Red shading, white font
B20 IF <>0 Light blue shading

Any way that the Condition and Format can be shown would be OK, so long as I can easily tell what they are.

When doing a Page Setup, you can elect to have comments printed as shown on screen or at the end of the sheet. I would like to have a method that would allow a similar listing of conditional formats at the end of the sheet, or on a separate sheet.

matthewspatrick
08-18-2006, 06:30 AM
Getting the list of cells with CF is easy, but you are not being specific enough on the formatting applied. On Excel's CF dialog, the basic categories of "font", "border", and "pattern" look simple enough, but behind the scenes there are potentially many objects and properties involved.

For example, when you say "Red shading, white font", there are at least two properties in play for the pattern (color and pattern applied), and for the font, there's the font name, italic, bold, size, underline, and color. If you are using borders, each border has its own suite of settings.

It's more complex than it looks, and so you need to be very specific about what you want--more specific than your last post.

mdmackillop
08-18-2006, 10:27 AM
Create a "library" of conditional formats on a hidden sheet. Have a Userform apply your choice to a selection.

lucas
08-18-2006, 03:26 PM
Nifty

Bob Phillips
08-19-2006, 10:22 AM
Not printing, but try this