Consider the following minimal example:
I want that two rows with the same group id have the same background color and that rows with different group id’s have different background colors. If I change a group id the colors should adapt automatically. I don’t want to specify which colors to use, this should be done automatically.
What is the easiest way to to this?
" I don’t want to specify which colors to use, this should be done automatically." This request will complicate things quite a lot. Is there a plausible reason for it?
Did you consider the implications? If yes, tell us something about the results, please.
The reason is that the table is large (20 - 40 rows) and the id’s may be changing, so I don’t want to think about which colors to use each time. However it would be ok to define say 40 colors once if this works on every new table afterwards without manual interventions…
The proper means in this arena should be Conditional Formatting. It has its disadvantages, though.
(Only felt so?) You can apply only named styles.
Well established usage requires an extra condition for everyone of the styles to be applied conditionally.
There are issues (“bugs”).
Another approach some would prefer and I woul discourage taking is programming.
There is an undocumented (?) method using CF I recently got aware of reading in another thread of one of the forums (I unfortunately cannot find at the moment): Use the style function inside an (the only then) CF condition in a way calculating conditions or choosing styles by other means. Evaluating it will apply the named style last touched in a calculation to the cell despite the overall condition (best) being never true. Though this is not in contradiction with the documented use of STYLE() I was surprised this worked.
Playing around with your example I couldn’t get it working under V4.3.3 but this may have been related to my user profile. Just having installed 4.3.4.1 the before created document is working well.
You will have to decide if you accept somewhat fragile constructs. See attached example!
Thanks @JohnSUN: I thought it must come from you. That was it. Do you also know a piece of documentation containing statements about this “abuse” of the condition formula?
Sorry, but unfortunately never seen documented limitations on use of the function STYLE() in conditional formatting. Yes, Calc begins to work very slowly, if it has to recalculate many formulas. But this applies to all formulas, not to the conditional format only. Or I have misunderstood your remark?
Thanks, JohnSUN! I didn’t worry about speed/efficiency in this case. I just tend to take the description of an UI object a bit literally - and that suggested to my kind of thinking: “abuse” of an element. The STYLE() function as applied within a formula calculating a result for a cell is at least described in the help. It is, of course, not specified in the ODF papers (as conditional formatting isn’t as a whole). Did you try my example and register the flickering position marker editing a CFed?
(Beg your pardon for the long commen!) From the help: STYLE / Applies a style to the cell containing the formula.
Does the cell contain the formula calculating a CF condition? I’m thinking a bit queer and old style, you see.
I think the cell may be owner of a process recalculating a CF, at least for a short time. This makes plausible the working of STYLE inside the condition. What about the flicker?
The flickering position marker? In Edit-mode? Yes, I see… With my example same thing happens. We can treat it as a natural disaster - as rain or a flock of mosquitoes. Sometime it will pass…
Lupp absolutely right (as often happens). For “don’t want to specify which colors to use, this should be done automatically” you need a macro. Take it - Cell colour.ods
@Wladislaw : That’s interesting, in specific the Russian remarks supplied with the code. Alas, I just understood стилей - and, maybe, one or two other words. Next boring day I should try to understand the working.
Wolfgang
Oops! These modules - ColorStyles and SortArray - were written as a response to the Russian-speaking forum. I just forgot to translate comments before publication here … I’m sorry!
“… I’m sorry!”
Don’t worry!