Romulus
09-27-2011, 08:18 AM
Hi all,
I have got this huge Excel file (*.xls, used on Windows XP, with Excel 2007), the file contains 3 sheets, one of them contains more than 246,000 formulas.
Even if there are so many formulas, there are "only" 40 different conditional formatting styles.
I need a VBA code that will search entire file, it will search each and every sheet and it will generate a report, a simple table, with following columns:
1). # - current number, e.g. 1, 2, ... n, where n is number of different conditional formats (in this case n = 40);
2). Range formatted - for instance A1:B22;
3). No. of CF conditions - number of conditional formatting conditions, e.g. from 1 to 3 (even if Excel 2007 could handle more than 3 conditions, I would limit the value to 3, because the file will be also used on other older Excel versions, which cannot handle more than 3 conditions);
4). Rule type - there are 2 possible values: Formatting or Formula;
5). Operator - one of these options: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to;
6). Formula 1 - complete formula for first condition, if the rule type is "formula"; if rule type is "Formatting", then the value for formula 1 should be "N/A";
7). Formula 2 - complete formula for second condition, if the rule type is "formula"; if rule type is "Formatting", then the value for formula 2 should be "N/A";
8). Formula 3 - complete formula for third condition, if the rule type is "formula"; if rule type is "Formatting", then the value for formula 3 should be "N/A";
9). Font & fill colour 1 - colour of the font and colour of the fill, if condition 1 is TRUE;
10). Font & fill colour 2 - colour of the font and colour of the fill, if condition 2 is TRUE;
11). Font & fill colour 3 - colour of the font and colour of the fill, if condition 3 is TRUE.
If there are sheets with no cells formatted, the report shuold generate sheet name and "This sheet contains no cells with conditional formatting".
I have searched a solution to this problem, I could not find it. Actually, there is a solution, Power Utility Pak version 7.1 (for Excel 2007 and Excel 2010), but that solution generates a list of almost 1,000,000 rows, with details for each cell. What I need is a consolidated version, the list should refer to each type of conditional formatting, not to each cell.
If there is a topic containing a solution to this problem, let me know. Please assist.
Thank you and have a great day :dunno.
I have got this huge Excel file (*.xls, used on Windows XP, with Excel 2007), the file contains 3 sheets, one of them contains more than 246,000 formulas.
Even if there are so many formulas, there are "only" 40 different conditional formatting styles.
I need a VBA code that will search entire file, it will search each and every sheet and it will generate a report, a simple table, with following columns:
1). # - current number, e.g. 1, 2, ... n, where n is number of different conditional formats (in this case n = 40);
2). Range formatted - for instance A1:B22;
3). No. of CF conditions - number of conditional formatting conditions, e.g. from 1 to 3 (even if Excel 2007 could handle more than 3 conditions, I would limit the value to 3, because the file will be also used on other older Excel versions, which cannot handle more than 3 conditions);
4). Rule type - there are 2 possible values: Formatting or Formula;
5). Operator - one of these options: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to;
6). Formula 1 - complete formula for first condition, if the rule type is "formula"; if rule type is "Formatting", then the value for formula 1 should be "N/A";
7). Formula 2 - complete formula for second condition, if the rule type is "formula"; if rule type is "Formatting", then the value for formula 2 should be "N/A";
8). Formula 3 - complete formula for third condition, if the rule type is "formula"; if rule type is "Formatting", then the value for formula 3 should be "N/A";
9). Font & fill colour 1 - colour of the font and colour of the fill, if condition 1 is TRUE;
10). Font & fill colour 2 - colour of the font and colour of the fill, if condition 2 is TRUE;
11). Font & fill colour 3 - colour of the font and colour of the fill, if condition 3 is TRUE.
If there are sheets with no cells formatted, the report shuold generate sheet name and "This sheet contains no cells with conditional formatting".
I have searched a solution to this problem, I could not find it. Actually, there is a solution, Power Utility Pak version 7.1 (for Excel 2007 and Excel 2010), but that solution generates a list of almost 1,000,000 rows, with details for each cell. What I need is a consolidated version, the list should refer to each type of conditional formatting, not to each cell.
If there is a topic containing a solution to this problem, let me know. Please assist.
Thank you and have a great day :dunno.