[Solved] More than 3 Conditional Formating conditions

[Unknown Mr]

Is there any way to get more than just 3 Conditional Formating Conditions?
I can not find anywhere where to...

[acknak]

Did you try a search? It might have brought you to this thread: http://user.services.openoffice.org/en/ ... =21&t=6039

If you wish to avoid macros and programming, you can use a lookup table to select a style based on the value of the cell.

If you need more specific help, I think we'll need some explanation of what you're after.

[Unknown Mr]

Wow! I could not figure that out...
I am new to this... And that looked like a ton of stuff I don't know, and wouldn't know how to do for a long time...

What I am trying to do is this, heres an example:

a1: no - b1: no - c1: no - d1: none
a2: yes - b2: yes - c2: yes - d2: done

I want to make all the yes' and done's have a green background, and all the no's and none's have a red background.
(and i'm sure you know this, if I change a no (with a red background) to a yes, it will turn to a green background)
And thats why I asked if there was a way to get more than 3 Conditional Formatings, because there is 1: yes, 2: done, 3: no, and 4: none

But I can not figure out how to do it... Is there an easier way than what you just showed me...?

[acknak]

Don't worry, I can't quite follow it either

See if this helps:

[Villeroy]

The only problem is that so few people understand how c.f. works with 1,2 or 3 conditions and a dialog.
The macro requires nothing but a named range where you put exactly the same formulas as you would use in an unlimited c.f. dialog.
There is absolute nothing special.
One row per condition:
1. The style name
2. One operator according to the symbols I provided (analogue to the operators in the dialog)
3. At least one formula expression without leading "=" but with quoted strings just like in the dialog (exactly as in the dialog)
4. Operators <=> (between) and >< (not between) require a second formula expression (exactly as in the dialog)

Set up as may conditions as you like (I tested a dozend or so).
Name the rows with 4 columns "readCF" since this is the range where to read the conditions from instead of the dialogue.
All the conditions apply to the currently active cell at the time when you call the routine "read_ConditionalFormats'". This is exactly the same when you hit [OK] in the conditional formattings dialog.

[Unknown Mr]

I got it!!! Thank you both of you!!!

[TheGurkha]

There is a simnple way to do it in this case, because by luck two of the conditions are similar 'No' and 'None', and you want the same formatting applied to both of these cases. You can combine these into one case.

See the example sheet and the screenshot.

Conditional Formatting Example.ods

(7.33 KiB) Downloaded 568 times

If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You can use the green tick icon at the same time if you like.

[Unknown Mr]

Thank you Gurkha I will keep that in mind for differnt projects to.

I guess I can say thanx to all 3 of you now!

[Villeroy]

Just one more comment on the currenly active input cell and why it may be important to notice:
The position of the currently active cell is important in respect to any relative references used in the conditions. When you call the dialog (menu:Format>ConditionalFormatting), you see the conditions with any relative reference being relative to the active cell and when you confirm the dialog you apply the dialog's conditions with relative references being relative to the currently active cell and likewise to any other selected cells.

Some example conditions:
Cell value is between MIN(scale) and MAX(scale) tests if this cell's value is within the range of a named range "scale". The position of the active cell is unimportant if the named reference "scale" is absolute, for instance "$Sheet1.$A$1:$A$99

Cell value is greater than $F6 The position of the active cell matters. If it is in row 6, $F6 refers to the cell in column F of this row.
If the active cell is somewhere in row 5, $F6 refers to the next row in column F and likewise all the other rows which may be selected too have a reference to the next row's value in F.

Cell value is between F5 and F6 with the active cell in E7. This tests if this cell's value (E7) is between the upper two precedents in the next column.
You get the above conditions displayed when you call the dialog while E7 is the input cell or when you call my routine "write_ConditionalFormat" while E7 is the input cell (the macro needs a named cell "write_CF" as target). Both, the dialog and the macro display the active cells conditional format settings.
When you edit and confirm the dialog, all the settings with their relative references apply to the same cell E7 since you can not change the active cell while the dialog has the focus.
Before using the macro "read_ConditionalFormat", you edit formulas with the active cell E7 in mind in respect to any used relative addressing.
Then you make shure that all your conditions are included in 4 columns named "read_CF".
Then you actually select the range(s) you want to format conditionally with E7 being the active cell and run macro "read_ConditionalFormat".

When using numeric constants in either one of the tools (dialog or macro) the application locale is the only relevant locale, regardless of any applied number format locale.
Condition "Cell value smaller than 3.14 has to be written as 3,14 (comma) if the application locale is a comma-decimal locale, such as German or French, regardless of any number format locale that may be applied to the cell. You are actually entering formulas without leading "=". Cell formula =3.14 uses the application locale too. Any comparison with constant strings needs double quotes since you are entering a formula ="X". Unquoted strings have to be valid names.

[raginggosu]

BLUF: Easy way to automatically change style based on number as demonstrated in my attachment

I know this is necro as heck, but this is still the first thing that comes up on Google when I searched for "more than 3 conditional format openoffice". I was able to do what I wanted with the following conditional format:

In File menu: Format -> Conditional Formatting
Condition 1 (checked):
Dropdown -> "Formula is"
Value:
STYLE(IF(B4<=1;"Red1";IF(B4<=2;"Orange2";IF(B4<=3;"Yellow3";IF(B4<=4;"YellowGreen4";IF(B4<=5;"Green5"))))))
Cell Style: Default

Where I made the styles: Red1, Orange2, Yellow3, YellowGreen4, Green5
To make styles Hit F11

You can either drag "example" cells into that box and name them, or right-click and create new ones. For example all I did was have them change the background color based on the number.

[lxvi]

@raginggosu not only is your post necro, but sorcery as well. I always thought that if you selected "Formula is" the formula had to evaluate to a boolean. But what does the STYLE function evaluate to??? Anyway, a tweak to what you're doing would be to put CHOOSE inside STYLE and then convert the raw value to an integer in the first parameter (ie the "index") of CHOOSE. In other words STYLE(CHOOSE(ROUNDUP(B4;0);"Red1";"Orange2";"Yellow3";"YellowGreen4";"Green5")). Obviously in a situation that doesn't neatly evaluate into an integer from 1 to 30, you are going to have to get more creative in coming up with a way to map your values....

[karolus]

@raginggosu not only is your post necro,…

@lxvi: Not ONLY his|her post is necro, …

[Alex1]

In TheGurkha's solution a cell containing e.g. "Noa" will be coloured red as well, which isn't intended.
Actually two conditions are enough if formulas instead of cell values are used.

[MrProgrammer]

Dropdown -> Formula is STYLE(IF(B4<=1;"Red1";IF(B4<=2;"Orange2";IF(B4<=3;"Yellow3";IF(B4<=4;"YellowGreen4";IF(B4<=5;"Green5"))))))

Warning: Using the STYLE function in a conditional format formula will trigger issue 113156 conditional format - page preview flickers

conditional format is recalculated nonstop

On my system, OpenOffice and the display server both run continuously at about 18% as long as the cell with that conditional format is displayed on the screen. CPU usage returns to normal if that cell is not visible. I believe conditional formatting is only evaluated for visible cells.

202310232112.gif (17.5 KiB) Viewed 3654 times

Alex1's method does not trigger the issue.

But what does the STYLE function evaluate to???

[STYLE] always returns the value 0

I always thought that if you selected "Formula is" the formula had to evaluate to a boolean.

The result of the formula is considered to be true if it is a nonzero number, and false otherwise: 0, any text value, or any error value. When true, the specified style is applied and conditional formatting is complete. When false, the next condition, if any, is tested.

Obviously in a situation that doesn't neatly evaluate into an integer from 1 to 30, you are going to have to get more creative in coming up with a way to map your values....

[Tutorial] VLOOKUP questions and answers

[cwolan]

Warning: Using the STYLE function in a conditional format formula will trigger issue 113156 conditional format - page preview flickers

conditional format is recalculated nonstop

An earlier report identified an issue with displaying comments (cell notes): Issue 109243.

Unrelated to conditional formatting, but worth mentioning: the STYLE function does not seem to handle the case of style names, whereas the Stylist does. E.g. in case of three style named "RED", "Red" and "red" the STYLE function will apply the same style depending on order of creation.

FWIW:
The title of this thread reads "More than 3 Conditional Formating conditions". In the More than 3 conditional formats topic, Jeff posted a link to the extension called CF+

Hello,

Today the answer would be: "Install LibreOffice instead of OpenOffice".

Or install CF+ extension

Regards