We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to color rows automatically depending on an id [closed]

asked 2014-11-18 18:48:54 +0200

student gravatar image

Consider the following minimal example:

image description

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-06 13:06:19.988154


" 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.

Lupp gravatar imageLupp ( 2014-11-18 18:54:52 +0200 )edit

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...

student gravatar imagestudent ( 2014-11-18 19:18:11 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-11-19 06:45:29 +0200

JohnSUN gravatar image

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

edit flag offensive delete link more


@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.


Lupp gravatar imageLupp ( 2014-11-19 11:14:42 +0200 )edit

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!

JohnSUN gravatar imageJohnSUN ( 2014-11-19 11:45:57 +0200 )edit

"... I'm sorry!"

Don't worry!

Lupp gravatar imageLupp ( 2014-11-19 12:19:07 +0200 )edit

answered 2014-11-18 22:26:50 +0200

Lupp gravatar image

The proper means in this arena should be Conditional Formatting. It has its disadvantages, though.

1) (Only felt so?) You can apply only named styles.

2) Well established usage requires an extra condition for everyone of the styles to be applied conditionally.

3) 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 the before created document is working well.

You will have to decide if you accept somewhat fragile constructs. See attached example!

edit flag offensive delete link more


I think that you mean this topic

JohnSUN gravatar imageJohnSUN ( 2014-11-19 08:44:34 +0200 )edit

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?

Lupp gravatar imageLupp ( 2014-11-19 11:00:12 +0200 )edit

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?

JohnSUN gravatar imageJohnSUN ( 2014-11-19 11:38:46 +0200 )edit

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?

Lupp gravatar imageLupp ( 2014-11-19 11:50:20 +0200 )edit

(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?

Lupp gravatar imageLupp ( 2014-11-19 12:01:15 +0200 )edit

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...

JohnSUN gravatar imageJohnSUN ( 2014-11-19 12:25:31 +0200 )edit

Question Tools

1 follower


Asked: 2014-11-18 18:48:54 +0200

Seen: 331 times

Last updated: Nov 19 '14