[Calc] More than 3 conditional formats

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Calc] More than 3 conditional formats

Post by Villeroy »

 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. 
Installation: Download the document ConditionalFormats.py.odt to a "trusted directory" according the the macro security settings. Open the document. Hit the install button. The script will be extracted to Scripts/python/pyCalc/ConditionalFormats.py within your profile folder.

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.
Attachments
MultiCF.ods
Example made with ConditionalFormats.py
(26.06 KiB) Downloaded 509 times
ConditionalFormats.py.odt
Document with installer for a Python macro.
(22.14 KiB) Downloaded 493 times
Last edited by Villeroy on Mon Apr 12, 2021 4:37 pm, edited 8 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

First positive feedback and inspirations from mpcengineering in [Solved] ### and #MACRO? on opening .xls spreadsheet
 Edit: 2008-06-28: Edited the above instructions. Now it's more or less a tutorial on c.f. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
zarkpros
Posts: 1
Joined: Thu Oct 09, 2008 6:51 am

Re: [Calc] More than 3 conditional formats

Post by zarkpros »

Thanks for all your work on this issue--I'm wondering: how do I use f(x) and what does that mean? I'm hoping it enables me to look at the info in one particular cell, and make the conditional formatting apply to the entire row that that cell is in. Will it let me do that?
OOo 2.3.X on Mac OSx Leopard + Vista, XP Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

The f(x) operator is the same as if you set "Formula Is" in the dialog. Use f(x) to evaluate things that are not related to the current cell you want to format.

Example testing if the current cell is greater than the average of some range:
Dialog: Cell value greater than AVERAGE(some_range) then "someStyle"
In the range: "someStyle" ">" AVERAGE(some_range)

Example testing if the cell in column B of the same line is greater than the average of some range:
Dialog: Formula is $B1>AVERAGE(some_range) then "someStyle" [call dialog with active cell in row 1 since the row is relative without $]
In the range: "someStyle" "f(x)" $B1>AVERAGE(some_range) [call macro with active cell in row 1 since the row is relative without $]

See also [Tutorial] Absolute, relative and mixed references. Absolute, relative and mixed references work likewise in validation and conditional formatting. It's a good idea to test your format conditions as cell formulas.
 Edit: zarkpros' issue has been solved in the old forum: 
More than 3 Conditional Formatting fields
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
witherj
Posts: 1
Joined: Thu Jan 15, 2009 4:57 pm

Re: [Calc] More than 3 conditional formats

Post by witherj »

This didn't work for EQUAL string comparisons (for me anyway). Here is a quick hack to ConditionalFormats.py which fixed this for me.

Code: Select all

46d45
< 
113,122c112,113
< 
<         # 15 Jan 2009
<         # Strings didn't work so this is a work-around
<         fml1_altered = fml1
<         try:
<             float(fml1)
<         except:
<             # probably text, not a value
<             fml1_altered = '"' + fml1 + '"'
<         oCE = _getConditionalEntry(fml1_altered,fml2,op,oRelPos)
---
>         
>         oCE = _getConditionalEntry(fml1,fml2,op,oRelPos)
125a117
>         

Attachments
ConditionalFormats_TextComparison.zip
Patched version for string comparisons
(1.76 KiB) Downloaded 1140 times
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

Thank you for this contribution. Yes, that should work more conveniently in most cases. Actually, my version works exactly like the dialog where you have to quote text values as well. I forgot to mention this glitch like so many others.
The respective properties of the condition are named "Formula1" and "Formula2", and in fact they are treated as if you were using the string with a leading "=" as a cell's formula. This way literal strings need to be quoted while unquoted XName can be treated as a named reference (absolute, mixed, relative named reference or database range). Your version does not allow for the latter anymore.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jane2geo
Posts: 1
Joined: Tue Aug 11, 2009 6:10 pm

Re: [Calc] More than 3 conditional formats

Post by jane2geo »

A fantastic solution for my needs! Thanks Villeroy.

Having success with both 6 and 10 formulas, but now need to add two more.
My problem is finding other working colors. I’m new to calc only started because
I needed this CF macro. Appears only the ten colors you provided work.
Is this accurate? can’t seem to use any other color hex codes.

Also could you please elaborate I’m using in excel, how to protect the cells with extended
conditional formatting.

using 3.1 on WXP
OpenOffice 3.0 on WXP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

Get familiar with styles, how CF works with styles and add your own styles. The "RGB_FFFFFF" in my files are just style names indicating the RGB color codes.
Thanks to acknak, who pointed out that the combination of function STYLE with CF obsoletes all the macro stuff: http://user.services.openoffice.org/en/ ... 553#p88553
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Calc] More than 3 conditional formats

Post by acknak »

Appears only the ten colors you provided work.
Is this accurate? can’t seem to use any other color hex codes.
OOo only works with named colors, so you have to define a new color that associates a name with each RGB value that you want to use. The name can be anything; using the RGB code as (part of) the color name is just a convenience.

Such color definitions are saved as "palette" files, and once you define the colors they can be used again. You can download more extensive palette files that define lots of colors as incremental series.

Once you have all the necessary colors defined, you can use them in your style definitions that are referenced in the conditional formatting dialog, or from the STYLE function.
AOO4/LO5 • Linux • Fedora 23
pcavaco
Posts: 3
Joined: Thu Aug 13, 2009 6:06 pm

Re: [Calc] More than 3 conditional formats

Post by pcavaco »

Hello,

I am really excited to start using this script! But have run into a snag.

How do I install python macros in OS X? I've attempted the Linux method of placing it my home foldre in a Scripts/python directory. I have been looking around Library/Application Support but haven't found any OOo stuff there.


Philip
OpenOffice 3.1.0 on OS X
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

As already stated: With the right styles and some fumbling around in test ranges you should be able to use more than 3 CFs combining the CF with function STYLE.

I have no idea about Mac's OOo and Python.
Do you see any macros in Tools>Macros>Organize>Python... ?
Translating this simple stuff into Basic should be a matter of an hour
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
pcavaco
Posts: 3
Joined: Thu Aug 13, 2009 6:06 pm

Re: [Calc] More than 3 conditional formats

Post by pcavaco »

Thanks for the reply.

I have the default python macros available in OOo but they seem to be packaged in the app file and not available in a directory. I will try creating an OpenOffice directory in the Library and see if OOo will see it.

I will experiment with STYLE too, it may do the trick.
OpenOffice 3.1.0 on OS X
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

I have the default python macros available in OOo but they seem to be packaged in the app file and not available in a directory.
Even on your Mac there should be some directory /Scripts/python/ somewhere in the installation path. This is where the example macros are stored globally for all users. Normally, that path requires administration access to drop files.
Your user profile belongs to you. Add a "python" directory in "Scripts" and drop your Python macros.

Under Linux one can link application macros to user macros, so you can edit them for other users of the same installation:

Code: Select all

APP_SCRIPTS=/opt/openoffice.org/basis3.1/share/Scripts/python
USER_SCRIPTS=~/.openoffice.org/3/user/Scripts/python
ln -s $USER_SCRIPTS/mymacro.py $APP_SCRIPTS/mymacro.py
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mgrivas
Posts: 4
Joined: Thu Mar 08, 2012 10:07 pm

Re: [Calc] More than 3 conditional formats

Post by mgrivas »

Thank you for your contribution. I did not prefer a macro that does the job, but I found extremely smart and ultimately useful the SCRIPT-version.
I have 10 styles, where one is the parent-style, one is the exception-style (errors or blanks) and the other 8 colour my sheet-lines according to the ROW() (even or odd), the team they belong to (value in col A) and the group (col B) .
With the SCRIPT in Conditional format, I have one Condition for normal colouring that is done by combining the name of the style using the several shell values and the MOD(ROW();2) combination. So I have 8 styles like

Code: Select all

team 0 group A line 0
, where team number is taken from col-A, group name is taken from col-B and the line (0 or 1) shows even or odd row. Then I make the condition (for row 12)

Code: Select all

STYLE("team " & $A12 & " group " & $B12 & " line " & MOD(ROW();2) )

I also made a second condition when A or B columns are blank, for the exceptions (errors or not-filled or whatever) .
I applied it to the whole row and then copied the formatting to the rest (copy - special paste -> only the format) .
In that case you do not need any python or other plugin.

The CPU overload mentioned in http://user.services.openoffice.org/en/ ... 41#p224141 and
in http://user.services.openoffice.org/en/ ... 58#p136558 does not seem to exist any more or at least in my LibreOffice 3.4.x in Linux .
LibreOffice 3.4.x @ Linux (Mint, Ubuntu, openSuSE)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

Well, yes. This is another variant of combining the STYLE function with conditional formatting.
So we can lookup a style from a lookup table and we can concatenate a style name from evaluated cell values.
LibreOffice 3.5 comes with a extensible dialog which takes more than 3 conditions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mgrivas
Posts: 4
Joined: Thu Mar 08, 2012 10:07 pm

Re: [Calc] More than 3 conditional formats

Post by mgrivas »

Yes, I am waiting for the "official" version of the 3.5 , but I suspect it will still miss the agility of a dynamically-built name for the desired style.
I mean, think of my case or any case where you need a number of different colouring schemes. You can easily built them with styles that have the proper names and the call them by assembling the name, while the 3.5-way would be to make a different condition for each case, right ?
LibreOffice 3.4.x @ Linux (Mint, Ubuntu, openSuSE)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

Recently I used this Python code to implement and test a stop-motion movie in Calc as discussed in http://user.services.openoffice.org/en/ ... 49&t=55857
Actually I never really used my own code before. Found 2 minor bugs and replaced the Python code in the above linked archive ConditionalFormats.py.zip.
If you want to use the macro with the stop-motion example, install it to directory <user_profile>/Script/pyCalc/ or point the blue push buttons to your actual location (I use a separate pyCalc directory for all Calc related Python macros).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kq76
Posts: 7
Joined: Sun Feb 12, 2012 12:36 am

Re: [Calc] More than 3 conditional formats

Post by kq76 »

Macro 'print_StyleNames' doesn't seem to print the last style created. My guess is there is a simple fix in the python code, but I don't know python.
OpenOffice 4.1.1 on Windows 10 (as of November 2015)
kq76
Posts: 7
Joined: Sun Feb 12, 2012 12:36 am

Re: [Calc] More than 3 conditional formats

Post by kq76 »

I figured out the problem others may have been having with text strings. I was starting and ending my strings with quotation marks, but it wasn't working. After lots of trial and error, finally with looking at the CF dialog box after trying the macro I noticed that my double quotes just weren't registering. So I did a test CF the old way, checked that it worked, then copied and pasted it in the third readCF column and I realized that those quote marks weren't the same as what was coming up when I put them in previously. By C&P they were straight up and down quotes, both in the input line and the cell, but with typing them in they were coming out as slightly angled to the right in the input line and with curls in the cells. Anyway, I tried it with just C&P and it worked. I don't know why they're different or whether those characters are technically different, but it only worked via C&P from the default CF dialog box.
OpenOffice 4.1.1 on Windows 10 (as of November 2015)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

This office suite automatically translates quotation marks into typographic quotes (aka curly quotes) assuming that you are writing text in human language for pretty printing. When you type a Calc formula with a leading = this feature does not apply since a spreadsheet formula is not human language.
Under menu:Tools>Auto-Correct, tab "Localized Options" you can turn off this feature for the whole office suite.
Alternatively you can assign language [None] on the "Font" tab on the formatting dialog which means that that the selected cells are not supposed to have text in any human language and so neither the localized auto-correct options nor spell check takes place.
For the whole document you can set the language of the default cell style which applies to all other cell styles where the same feature has not been set otherwise.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Wheelz
Posts: 28
Joined: Tue Apr 27, 2010 8:34 pm
Location: Wanganui, New Zealand

Re: [Calc] More than 3 conditional formats

Post by Wheelz »

Forgive me, I'm not overly familiar with Python but can sometimes fumble my way around.
"Both methods, built-in dialog and this macro, evaluate the conditions from top to bottom until a condition evaluates TRUE."
In my case I want ALL conditions to be true. Is there a way to code it so it evaluates them until a condition evaluates FALSE?

Thanks
Open Office 3.2
OS X 10.6
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] More than 3 conditional formats

Post by Villeroy »

Wheelz wrote:Forgive me, I'm not overly familiar with Python but can sometimes fumble my way around.
Don't do that. Just save my module in your macro directory and use it AS IS. I wrote this long before LibreOffice came up with its modified GUI for conditional formatting and I still find a rectangle of cells more intuitively than any kind of dialog.
Wheelz wrote:In my case I want ALL conditions to be true. Is there a way to code it so it evaluates them until a condition evaluates FALSE?
Wrap your conditions in NOT(arg) so all True becomes False and False becomes True.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply