Introduction

Excel has a wonderful facility named 'Conditional Formatting', which enables you to test a range of cells, and if any cell within that range meets a specified condition, a highlighting format can be set.

Whilst it is an excellent concept, and a very useful facility, it suffers from one major and one less major drawback:

• An Excel spreadsheet is limited to just 3 Conditional Format (4 if you count the range's default format)
• The built-in dialog supports a selected range of formatting options, e.g. font type changes are not possible.

To have more than 3 conditions has typically involved adding customised VBA event code to your workbook. But no longer, there is now an Excel add-in that provides that ability to define more than 3 conditional formats, in fact up to 30 are catered for.

Once successfully installed, you should find a new menu item, 'xld Tools' before the Help menu. From this menu there is a CFPlus sub-menu, which contains a Launch CFPlus option , as well as Special Functions, and the usual ancillaries.

Versions Available

CFPlus v1.02.100 - Beta release	Install both the .xla file and the .ini file in the same directory, and install the add-in from there. As a Beta release, this version of CFPlus may be liable to stability problems. Please report any problems encountered, or failing functionality. Download here (322Kb).

Features and Benefits

Multiple Conditional Formats, upto 30 in total for any range of cells.

Enhanced Formatting, all of the formatting options provided by the built-in Conditional Formatter, plus more besides

Simplified Maintenance of the conditions via an easy to use main dialog that lists all of the conditions, and provides add/delete/edit buttons, navigation, and move functions

Range Highlighting of the targeted ranges, with one click display of the condition(s) being tested

Special Conditions that simplify the task of creating some of the more frequently requested complex conditions, such as row striping, max/min values, date conditions, etc.

Format Copy & Paste to allow fast, simple copying of the CFPlus formatted cells

Multi-language support is provided by CFPlus. A few languages are provided in the download (see the Help for the languages supported), and further languages can be added by any user by adding to the INI file. The default language that the add-in assumes can be overridden

Launch Hotkey to provide keyboard launch of CFPlus.

Various Launch Options are provided by way of a full toolbar menu, optional right-click menu, and an optional toolbar icon

Context Sensitive Help File, in HTML Help file format

Configurability allows each user to define their default mode, language, and hotkey.

Changes/Bug Fixes

v1.02.100 Beta	1. a few (relatively) minor bug-fixes 2. now handles direct loading of add-in rather than installing 3. changed the date check special condition formula 4. adition of French language version
v1.01.090 Beta	1. added a better dialog to show the formats applicable to a highlighted range (thanks to Harlan Grove for that suggestion) 2. fixed a bug in the format copy/paste function 3. fixed the Error 5 that some users of Win95 and WinMe were getting
v1.01.080 Beta	1. amended the main condition list form to be resizable.(thanks to Peo Sjoblom for that suggestion) 2. tested for an active workbook when CFPlus launched, to avoid error

Platforms

	Excel 97	Excel 2000	Excel 2002/XP	Excel 2003
Windows 95	Yes	Yes	?	?
Windows 98	Yes	Yes	?	?
NT4	Yes	Yes	?	?
Windows 2000	Yes	Yes	?	Yes
Windows ME	?	?	?	?
XP Home	Yes	Yes	Yes	Yes
XP Pro	Yes	Yes	Yes	Yes
Windows Server 2003	?	?	Yes	?
Mac	?	?	?	?

Key:
Yes - tested and working okay:
No - tested. but with problems:
? - not tested

It is unlikely that it works with the Mac OS, due to Windows API calls.

This add-in has also been tested with Citrix Metaframe, with Office XP and Excel 97. In this environment, installing addins depends on the Metaframe settings, so this needs to be set correctly.

We would appreciate feedback on any Excel/OS combinations that we have been unable to test.

Known Bugs

The following is a list of known bugs, so if you come across any of these, please do not report it again.

None as yet.

Further Details

There are a number of considerations regarding this add-in that you should be aware of:

Only 30 (sic!) conditions are supported per range by CFPlus

Performance is impacted in the same way as any workbook with multiple formulas, and/or event code. A spreadsheet design that requires constant and complex re-calculations will take time, so design your spreadsheet carefully

Number of ranges is limited to 100 per worksheet

Copy/paste of these formats may not work for complicated conditions

Workbook Event Code is added to the target workbook to handle the formatting. This event code calls into procedures in the add-in, which means that if you share the workbook, and your colleague will be updating the data, they will also need to have CFPlus installed. If your colleague just wishes to view the results of your work, the format will remain without the code or the add-in installed

The Registry has new keys added, under the key 'HKCU\Software\xld\CFPlus'.

Uninstaller

Whilst it is a simple matter to uninstall CFPlus, it is just an Excel add-in, CFPlus also installs code into the target workbook which remains even if CFPlus is uninstalled. This can become an annoyane as it checks whether CFPLus is installed, and warns you if not.

A function will be added to CFPlus to remove this code from a nominated workbook, but in the meantime, you can download a small executable to remove the CFPlus code .

Terms Of Use

This product is freeware. This means that you may download it, and you may use it for free, but the intellectual rights are retained by the authors.

All rights reserved. No content, executables, programming code or images may be sold without the express permission of the authors. In most circumstances, this limitation extends to publishing or distributing the code, but some of the more interesting code is offered under 'open source' terms for you to analyse, improve upon, or distribute. At some future time, it is intended to provide articles explaining how and where this code is used, but at present it is just offered 'as is'. Four code modules are povided, namely:

• mEventCodeHandling
• mNames
• mGlobal
• mVBECoding

A zip file containing this code can be downloaded here.

The authors cannot be held responsible for ANY damage or any loss resulting from the use of this add-in, or the formulae generated by it.

Technical Support

As the add-in is provided as a free download, no technical support of any sort can be provided. Any errors or faults reported will be investigated, and as and when fixed will be included in later releases, but no individual response will be offered.