Contents
Conditional Colour Excel Add-In

Contents

About Conditional Colour

The Conditional Colour Menu

Contacting the Author

Version History


About Conditional Colour

Conditional Colour is an Excel Add-In which allows the conditional formatting of the text colour of cells dependant on their contents.  It allows for many ranges to be set up with different conditions each.  Up to two conditions and a default are allowed for each range, although more conditions can be made by applying conditional colour twice to the same range, with different conditions.


Adding a Conditional Colour Definition
Adding a Conditional Colour

To add a new conditional colour select the range to which the conditional colour should apply, and choose Add from the Conditional Colour menu (it can be found under the Format menu).  You will be presented with a dialog box in which you can specify the range, which will be automatically filled in with the range you had selected when you chose the add command, and the conditions, and the colours.

To set a condition type it into the condition box, and choose the colour for that condition into the corresponding colour box, for example if you wanted the text of a cell to be blue if its contents were greater than 3, then you would type >3 into the "Condition 1" box.  To choose the colour from a list press the button next to the colour box to show the colour picker.

To choose the colour simply press the button beneath the colour you want to choose.  The colour number will be automatically entered into the colour box.  The second condition and colour should be put into the "Condition 2" and "Colour 2" boxes in the same way.  If the second condition overlaps the first condition, for example if condition 1 was >3 and condition 2 was <5, then condition one overrides condition 2, and if the cell contained 4 colour 1 would be applied.  In the "Default Colour" box enter the colour which should be applied if neither of the conditions apply.  Leaving the colour box blank, or specifying colour 99 by either typing it directly or by choosing the bottom right colour from the colour picker stops the cells colour from being changed when the condition applies.  This is useful if more than three conditions are required.  For example, if the conditions required were <0, =1, =2, >5, =16, and all had to have a different colour then first the cells should have a conditional colour format applied to them in which the two conditions were <0 and =1, and the default colour should be black, or whatever you prefer.  The same cells should then have an additional conditional colour format applied which has the first condition =16 (remember the first condition takes precedence, so if it were >5 then as 16 is >5 it would have colour 1 applied if >5 were the condition 1), and second condition >5.  The default colour should be 99, or blank.


Removing or Editing a Conditional Colour Definition
Removing or Editing a Conditional Colour

To remove or edit conditional colour formats choose the Organise command from the Conditional Colour menu (found under the Format menu).  This will show a dialog box with a list of all the currently set up conditional colour formats, and allow editing, removing, or addition.

To remove a format simply select it and press the Delete button.  To edit a format select it and press the Edit button.  Note that the range is now displayed with a sheet reference in addition to a cell reference.  This is optional; if it is not specified then the previous sheet reference will be used.  (If there is no previous sheet reference, such as in the case of adding a new format area then the currently displayed sheet is used).


Contacting the Author

Conditional Colour was written by Alex Vallat 1998.  Please note that if using Excel 5 you can not display the colour picker from the Edit dialog box when displayed via the Organise dialog box.  Also note that in the current version, 2.3, Conditional Colour has just reached 'Critical Mass', and so is more likely to have small bugs which are easily fixable, so please have patience, and contact me to let me know about as many bugs as possible.  If you leave an email address I will email you a fixed version within a couple of days.  Contact me for bug reports, comments, suggestions at this email address: AlexV@ComPorts.com

You can visit my website at http://www.ComPorts.com/AlexV


Version History
Conditional Colour Version History

•  Version 1.0

Original Prototype

•  Version 2.0

Made into Add-In, added documentation, added Colour Picker.

•  Version 2.1

Debugged Colourify engine to correctly handle text and null cells.

•  Version 2.2

Added Excel 5 compatibility, added renameability of xla.  Debugged Colourify engine to accept "None" condition.

•  Version 2.3

Updating options added.  Initialisation file CColour.ini added (to windows folder)


The Conditional Colour Menu

The Conditional Colour menu can be found under the Format menu, as shown in the picture above.  For more details on the commands, click on the links below.

Add New

Organise

Update Now

Disable / Enable Updating (The menu text will change according to the current status)

Updating Options


Updating
Updating Options for Conditional Colour

Update Now

Selecting this command will update the colour formatting of all sheets irrespecitvely of any other settings.

Disable / Enable Updating

Selecting this command will toggle the general updating status.  If updating is disabled then the menu command will read "Enable Updating", if it is enabled the command will read "Disable Updating".  When it reads "Enable Updating" colour formatting can only be updated by selecting the "Update Now" menu command.

Update Options

Selecting this command will show the Update Options dialog box.

To select updating options for any sheet which is already open simply select it from the drop-down list, then set the checkboxes according to when you want it to automatically update the formats.  If the checkboxes appear ticked, but shaded in grey it means that the sheet has not yet had conditional colour formatting applied to it.  If you change these to ticked or unticked then when that sheet has conditional colour formatting applied to it it will have those settings, rather than the default for new sheets.  For those which you don't set previously, the updating settings for a sheet will be set when you apply any conditional colour formatting to it for the first time, according to the settings you set under "[Default for new sheets]".

Clicking the "Set for All" button will set all the sheets in the list, including the default, to have the same settings as the currently displayed one.