Edit: 2020-07-21: Replaced the Python code with a slightly enhanced version. Now it is compatible with Python3 (LibreOffice). The code is installable from a text document now. |
Calc cells can take more than 3 conditional formats, but the GUI allows for only 3 in dialog Menu:Format>Conditional Formatting ...
Writing another GUI is very tedious. I decided to write a set of very simple macros to read conditional format settings from a named range 'readCF' and apply them to the currently selected cells.
Once you applied extended conditional formatting no macro nor helping range is needed anymore. You may even save the file as xls and load it fully functional in Excel.
Named range 'readCF' can be anywhere in your document as long as it refers to an absolute address like $SheetX:$A$1:$D$12 (5 dollar signs). Just select the range you want to use for setup of extended c.f. and type "readCF" (without quotes) into the name-box left of the formula bar.
'readCF' needs to have 4 columns:
Column 1: Style name
Column 2: One of following operator symbols:
= Cell value equals the following expression
<> Cell value does not equal the following expression
< Cell value is smaller than the following expression
<= Cell value is smaller than or equals the following expression
> Cell value is larger than the following expression
>= Cell value is smaller than or equals the following expression
<=> Cell value is in between the following two expressions (including both expr.)
>< Cell value is not in between the following two expressions (excluding both expr.)
f(x) The following formula returns TRUE (number <> 0). This can be used to test conditions independent from the current cell.
Any other value in the operator column skips the respective row.
Column 3: A value or formula expression without leading "=". Literal strings need to be double-quoted, otherwise they will be interpreted as range names.
Column 4: A second value or formula expression without leading "=". Used with operators <=> (between) and >< (not between). Disregarded for all other operators.
The following rules apply to conditional format settings set by the built-in dialog as well as by readCF range:
- When using relative references in formula expressions, bear in mind that relative references are relative to the selected active cell. In other words: Both methods, built-in dialog and this macro, set the conditional formats for the single active cell of a range selection. These settings are copied across all the other selected cells, adjusting any relative reference acordingly.
Please perform the following demo on an ordinary blank sheet to clarify this important feature: Select some rectangle of cells (say A1:D4) notice the position of the active entry cell (say D4), type =$X4 (or $X1 if the entry cell is in row 1) and finish the input with Alt+Enter rather than Enter. This enters the same reference into all selected cells. Each cell refers to the cell in the same row of column X. This is exactly how relative references behave in conditional formatting, no matter if you use the dialog or call my macro. All relative references are relative to the currently active cell, and will be expanded to all selected cells.
- All conditions are taken as literal formula strings without the leading "=". Any textual condition having a literal string "John Doe" needs to be entered with the double-quotes just like you would do in a cell formula =X1="John Doe".
Unquoted B4 refers to the cell B4 relatively to the active cell. Unquoted $B$4 refers absolutely to cell B4. Unquoted AnyThing refers to a named range AnyThing. Unquoted numbers are treated as numbers. Literal date values need to be entered as quoted ISO strings ("2013-12-31") or as formula expression DATE(2013;12;31.
- Function names and decimal separators (comma or point) must comply with the application wide locale in Tools>Options>LanguageSettings>Languages>Locale
Both methods, built-in dialog and this macro, evaluate the conditions from top to bottom until a condition evaluates TRUE. If condition "Style1 if cell value < 1" comes before "Style0 if cell value < 0" Style0 is never applied since "< 0" always matches earlier or none of them matches (if not < 1 it's < 0 neither).
In short terms: Just setup your conditional formattings exactly as you would do in the dialog, keeping in mind the active cell of the later selection for relative referencing. Formula expressions work exactly like in ordinary cell formulas except for the the leading "=" which is not required since the conditional expression is treated as a formula anyways.
Have you setup your 4-column range and named it 'readCF'? OK, it's time to select cells and call Python macro 'read_ConditionalFormats'. Menu:Tools>Macros>Organize>Python...
This will read your range 'readCF', apply the settings to the currently active cell and copy across the other selected cells. Multiple range selections are allowed.
You end up with independent conditional formattings that do no longer relate to any macro or helper ranges. You may redefine range 'readCF' and apply the new settings to other ranges without losing the previously applied conditional formats. Finally you may remove all helpers and pass the document to someone who has no macro installed. The conditional formattings keep on working. Of course you can multiply existing conditional formattings by simple copy&paste.
What happens when I open the original dialog? Well, you see the first 3 conditional formats. If you confirm the dialog [OK] you will lose all subsequent conditions. So take care to [Cancel] this dialog. One experiment with Excel'97 shows, that this version can handle more than 3 conditional formattings, but it crashes when you call the respective dialog (what does this lack of checking tell us about the quality?)
Little helpers:
Macro 'print_Operators' prints a vertical list of the above described operators (=, <>, ...), starting at the active cell. You may use this list with list-validation in the definition range 'readCF' (see example document).
Macro 'print_StyleNames' prints a vertical list of the current document's cell-styles, starting at the active cell. You may use (parts of) this list with list-validation in the definition range 'readCF' (see example document).
Macro 'write_ConditionalFormats' prints a list of the active cell's conditional formatting to a cell named 'writeCF' (see example document).
More Tips:
- After running "read_ConditionalFormats" the helper range "readCF" is no longer needed. You may want to reuse it for other sets of condtional formattings in this document. It's OK to keep some empty rows if the readCF is taller than required. Resize "readCF" if you need more conditions (insert rows).
- You can copy cells with extended c.f to other cells.
- Keep a sheet-template with prepared range names and styles similar to the first sheet in the attached example spreadsheet. Menu:Insert>"Sheet from file...". Once you successfully used the helper sheet you may hide or even delete it.
- Protect the cells with extended conditional formattings. This disables the c.f. command in Excel and Calc. So you eliminate the risk to crash Excel by calling it's dialog for c.f. Calc's c.f. dialog will not crash the application, but you should cancel it. Confirmation truncates the c.f. to the 3 displayed ones.
That's it for now. Have fun with interestingly formatted spreadsheets.