How do you automatically set the background color of an entire row based on the value of a specific column in that row in OpenOffice and LibreOffice?

There are similar questions for Excel, but this doesn't completely work in LibreOffice.

I can set a conditional formatting rule using a formula to set the color of a specific cell based on the value in that cell (e.g. =$D5), but how do I apply the formatting to the entire row?


It's even easier than pnuts' solution. You don't need to select the cell that holds the value that should be relevant for conditional formatting. Just select all the cells that should get conditionally formatted, and use a formula-based rule. Now, if your formula uses a cell address with fixed column (e.g. '$D5'), OpenOffice will adapt it for every selected cell.

For example: You want to conditionally format the following table based on the value of the second (B) column (format should be applied if value is greater than 2):

Conditional Formatting 1

To do so:

  1. Select the cells A1 to C5;

  2. Select Menu Format -> Conditional Formatting ->

  3. Hit the Add Button to add a condition;

  4. Select condition type Formula is

  5. Enter as Formula $B1 > 2 and set the format to be applied if condition matches (for example, ugly red background);

The result will look like this:

Conditional formatting: result

To double-check what LibreOffice / OpenOffice did with your table, select a single cell, for example A4, and select Menu Format -> Conditional Formatting ->
Manage... again.

You will see there's a conditional formatting rule defined for that cell, with Formula is as condition type, and $B4 > 2 as formula. So, LibreOffice translated the conditional format defined for the complete table in single rules for each of the cells automatically.

| improve this answer | |

I confess I found this remarkably tricky. You need to 'juggle' the selected cell (black outline) with the selected array for formatting (shaded).

Click on D5 (to show black outline) and select entire sheet (above 1 and to the left of A). Set conditional formatting required with Formula is: $D5={whatever the contents of D5}.

If that does not work it is only that I have not explained myself properly!

| improve this answer | |
  • 2
    Thanks Pnuts, your answer worked ! Here is what I did: 1. Click on cell that holds value on which color depends. Make sure it is in one corner of 'table' you want to apply coloring to. 2. Then select entire range either with mouse (starting from previously selected cell), or with holding shift key and clicking on diagonally opposite corner of the range. 3. Enter conditional formating and define condition as "Formula is" "$D5 = 'whatever condition is'. ("$D5" is reference to a corner cell holding criteria value) That should work :) – PopMilo Aug 7 '13 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.