Conditional formatting based on formula addressing current row in LibreOffice Calc

2
votes

I have a column of cells in LibreOffice Calc with conditional formatting to apply a style to those cells.

The conditional formatting is programmed to format the cell if the following formula is true:

AND(B106=0,C106=0)

The trick is that instead of always evaluating this formula for row #106, I would like to evaluate the formula for the current row.

For example, in cell A1, I would like the conditional formula to be

AND(B1=0,C1=0)

And in cell A2, I would like the conditional formula to be

AND(B2=0,C2=0)

What I'm looking for is to program the entire column with a conditional formula like

AND(BCURRENTROW()=0,CCURRENTROW()=0)

but obviously that syntax is incorrect.

How can I accomplish this?

2
conditional-statementsformulaconditional-formattinglibreofficelibreoffice-calc

2 Answers

4
votes

A conditional formatting based on a formula has two settings that determines where and how it applies.

First is the Cell Range to which it applies to. If this is

Range : A1:A1048576

then it applies to the whole column A.

Second is the formula itself. Precisely whether cell ranges in that formula are relative or absolute or mixed. As in all other formulas, a relative cell reference is A1 for example. A absolute cell reference is $A$1 for example. And mixed cell references could be $A1, where column A is absolute but row is relative, or A$1, where column is relative but row 1 is absolute.

So a conditional formatting applied to range A1:A1048576 and having formula AND($B1=0,$C1=0) should fulfilling your requirement. As you see the formula always gets columns B and C (absolute) but gets the row in which it is actually calculated (relative).

Example:

enter image description here

0
votes

If you want to paint all row with a color when the column "B" CONTAINS a value, for example "XXX" you can do this:

Format-> Conditional Formatting (Add)

We select "formula" and we put: SEARCH("XXX";$B2)>=0

We apply the style "Good" (To view selected rows in green)

And we select the matrix in we want actuate (important!), by example : A2:H109

Now we see all the rows where the column B contains "XXX" in green.

enjoy !