2

This is driving me crazy. I try to set up the simplest conditional rule, but it will not work: format cells based on their text value.

The sense is to colorize a separator line. Until now I had static formattings, but when sorting the table, the formattings stay on their position which is .... silly.

Here is my attempt to solve this issue with a conditional formatting rule. It does format cells, but not the right ones! It seems to format empty cells - I don't know why.

Here is a screen: enter image description here

And please don't flame on me to read the help!! I tried this. But it is translated really bad and describes completely different scenarios with numbers and not text and there is no explanation of the field "Bereich" (=area) within the conditional formattings dialog, except an explanation like "this is to define the area", well ... thanks for that... :D

I really don't get it. Please advise! :)


Update:

With the help of tohuwahowus great answer, I was able to realise what I wanted to do - visually highlight a separator line that always is placed between two logical parts of the list, even after being sorted with multiple sort conditions: solved screen

The originally screenshotted conditional formatting rule is altered now additionally to act as "contains" condition, to be able to prepend a character (K) for achieving the sorting goal (between "ja" and "nein"). :) The list is sorted first by column F, second by H and third by column C.

2
  • 1
    What do you mean by colorize a separator line? Are you trying to have every other line colored, or do you want lines colored if they have no values at all in the 'Agiorgitiko ... Canaiolo' fields
    – mcalex
    Nov 13, 2014 at 3:54
  • Though already answered, I want to clarify the scenario: I did not wanted to apply the commonly asked formatting of colorizing every other row. The challenge was to colorize only those cells with an exact special content, to be able to prepare the corresponding row for a fixed position within a multi conditioned sort criteria - which should result in letting this row act as visual separator line between two parts of the list (in my actual case: available vs. historic wine bottles). And no, I did not wanted to colorize empty cells! This is what happened unintentionally. :D
    – Nicolas
    Nov 13, 2014 at 22:20

1 Answer 1

1

In the Conditional Formatdialogue, just put the string @SEP@ in quotation marks: "@SEP@" (see below, green rectangle). This is required to make LO handle it as string; otherwise, it would try to handle it as a formula:

enter image description here

Take care that if you choose equals / ist gleich for comparison (above: red), the comparison is case-insensitive - so LO Calc will apply the formatting for cells holding @SEP@ as well as @sep@. If you want to handle both strings differently, you'll need to select starts with / beginnt mit as operator.

1
  • Thank you very much, this was the missing piece of the puzzle! Often, after being teached with the solution, it seems obvious. Not in this case (at least for me). I still have no clue of how I would have been able to figure this out by myself, so hopefully this Q&A (especially your great explanation) will help a lot more people!
    – Nicolas
    Nov 13, 2014 at 22:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .