Conditional formatting, calc, current row reference not working

I’m trying to conditionally color the background of a cell based on whether or not other cells in the same row contain a value.
I can set the format for a single entry (e.g. for cell B14) as follows:

Select cell
Formula is
Apply Style  MyStyle

However, when I try to generalize the formula to propagate to other cells, it doesn’t work:



For B14 set Formula is…


This will set your desired style whenever

  1. the cell is not the only non-blank cell in the row and
  2. somewhat redundantly, but to be clear, the cell itself does have content.

It seems to copy/paste/paint okay for me on quick tests.

That’s not what I want. I want Bn’s (cell B in any row) format based on Fn being non-empty and Gn being empty; and do it regardless of whether Bn has content or not.

As noted originally,


works for a specific cell in a specific row (B14); I want an expression that will work for Bn (i.e. cell B in any row). So the row number cannot be explicitly enumerated.


Practically speaking, simple addresses in conditional formatting are just relative offsets based on the address of the selected cell at time of entry, or basically the top left cell of a single conditionally formatted region.

Do you mean to always check F and G, or do you mean to check 4 and 5 cells to the right of the conditionally formatted cell?

If you just mean 4 and 5 cells to the right–for example if you are just meaning to copy the formatting down the B column–then LO Calc will generalize the formula for you and your formula should work fine. Just copy or paint down the formatting. Note: you may need to do a recalculate (ctrl+shift+F9) before you will see the correct formatting after expanding your conditionally formatted region using a fill or copy operation. [I can’t tell you exactly what situations lead to this, but it does show up as a bit of a glitch at times.]

I mean for Bn, always check Fn and Gn.

How do I paint-copy only the conditional formatting for cells? If I select B14 (which has the proper conditional formatting), is there some ctrl-shift-meta combination that I can drag down with?

But I am pretty confused:

I have discovered I can get what I want by:

select B14 (which has the correct conditional formatting)
click format/conditional/manage...
select the range containing the formatting I want
click edit
extend the range to include additional B cells

But that seems rather awkward.

When I do the above, I get what I want for a range of B cells, but the conditional format itself still refers only to F14 and G14; it doesn’t have an expression referencing the current row. How does LO know to use Fn and Gn for the format of Bn, when the expression uses F14 and G14?

See @eeigor for further explanation about:

In Excel (or really, LO in general context) a reference to B2 within the cell A1 just means over one and down one. It’s really just “code” for B=2 - A=1 which is 1 and 2 - 1 which is 1. In Conditional Formatting the same applies, but it is always as if the top left cell of the conditional formatting range is a “stand in” for whatever cell is being checked. So in the case of A1 referencing B2, every cell will check over 1 and down 1.

As for copying the format:

  1. Use format painter. This at least works if you are painting contiguous cells. LO Calc will automatically combine them into a single range, just like you are editing by hand. If you have different formats you otherwise want to preserve while overlaying the conditional format, well, then, yes, you may need to do hand editing on the Conditional Format dialog–but this would be really rare and even ill-advised going down a column in 99.9% of spreadsheets, anyway.
  2. That said, I personally would assign a keystroke to the Conditional Format dialog and come to love it. I start almost every…and edit almost every…conditional format via the dialog. Otherwise it is easy to get confused ranges, missed cells, etc.
  3. Do be aware, however, that if you have multiple mostly-identical sheets (like monthlies), then it may be difficult to change conditional formatting across all of the sheets. If you have one sheet set up then copy, you’re okay. But if you then need to make changes or additions to the conditional formatting you may be applying that one sheet at a time, short of writing a macro, which LO will not successfully record for you.

The formula is correct, but column references may be made relative if you want to freeze the columns:
Range: B14:B16
Formula is AND(NOT(ISBLANK($F14));ISBLANK($G14))

Do for one row, then copy the format of the cell in it (column B) using a brush, and extend to other cells in column B. Ideally, the range should be continuous (eg., not <E14;E15;E16> for 3 cells), but when formatting multiple ranges without gaps, it will.

This is nonsense. Everything is calculated without your participation. No ROW functions needed.

The problem is that you don’t understand the meaning of relative references.
F14 means that it is in the current row of the current column, that is, RC, not R14C6 in Excel notation as you think. Do you understand the difference?
A relative reference is made to the first cell (cells in a row) of the range, and then it becomes the source position for calculating other references (offsets).
For example, for B15 it would be F15, that is, R[1]C, where 1 in brackets means an offset of 1 cell down from the source position.
Switch to Excel R1C1 formula syntax (grammar), and everything becomes clear.

Ok, thanks, I follow you. However, what if I want a formula I can copy and paste anywhere, without regard to the original row? In that case I need a way to refer to the current row. In other words, I want an expression I can paste into ANY cell in a row (other than F and G), to express the condition “column F is not empty AND column G is empty”.
After some head pounding, it appears that the following works:


Is there a more compact way of expressing this?

You just have to refer to the source position, as I have shown.
Your method was correct:
if row 14 is the upper row of the range and F & G are the left columns of that range. Something like this…

CFormat.ods (11.7 KB)
It’s your way. And that it true.

Have you done it?

No F & G and others. See:

You will have to learn to understand the Calc A1 style in relative address mode.

Thanks for your patience. To simplify things, lets forget about conditional references. I’ve attached a sheet with 4 cases, and a simple expression in each of the first two cells: =ISBLANK(RCn), where n is either 3 or 4. It’s my understanding this is an absolute reference to the current row, cell n. But in all cases the expression returns TRUE. Obviously I still don’t get it.
CellRefs_1.ods (9.0 KB)

My error, the refs in that sheet should be something like
where $D means absolute column D, and 1 means the next row?
But then how do I reference the current row? $D0 doesn’t work, nor does simply $D.

Edit: I think I see now…
A row number is always an absolute row number, but without the $ it really means “subtract the row number in the formula from the row number where the formula originated to get an offset, then add the destination row number”. So the number in the formula is always an actual row number, which without the $ is used to compute a new row number whenever the formula is copied.

For example, the entry $D3 in row 7 means the row offset is (7-3) = 4, and when pasted into row 12 would become (12-4)=8, and the formula in row 12 would appear as $D8

CellRefs_1 (2).ods (16.3 KB)

A whole bunch of ranges. Do you realize that the formula is the same everywhere?

And R1C1 style should be turned on and all formulas will change by themselves, and you in Calc A1 mode started entering references like RC…

And the cells to be checked are 3 and 4 columns apart from any cell (wherever it is) to which the conditional format applies.

1 Like

Thanks both for your patience and explanations.