Calc howto: conditional format a row based upon a column

I have some data to analyze, now if have to check for some values in a column.
If the column contains 6008 the row should be red, and if its 7003 it should be green, 5008 orange, etc
And well some more colors based upon values on that single column.

How do i accomplish this ?

(i have no experience with macro’s in calc, i am a programmer, but using it just for 2 weeks)

1 Like

You can do this with a “Formula is” condition in conditional formatting.

  1. Select all the cells you want the formatting to be applied to, for example B1:E3000
  2. Format → Conditional Formatting → Condition
  3. Change condition type to “Formula is”
  4. In the field next to it, type in a formula that will set the style if true. For example: $A1=6008
    This will make all the cells in each row to check the value of the cell in column A of that row.
  5. Select your desired style, and press OK

Repeat for as many styles and specific values as you need.

To copy the conditional formatting to another file:

  1. Create an empty sheet and set the conditional formatting (or duplicate a sheet with the formatting and remove all data)
  2. Open both files at the same time
  3. Copy the empty sheet (with the conditional formatting) from the first file to the second file
  4. Copy all the data you want formatted onto the new sheet
  5. If you need to fix cell ranges for the conditional formatting, go to Format → Conditional Formatting → Manage, click Edit, and edit the Range value.
2 Likes

Someone upvote this!

I agree, but don’t yet have the points to do it.

Excellent answer. That’s what I was looking for. How to format entire row basing only on one cell in that row. Many thanks to Jaroslaw Filiochowski for detailed explanation. Great job!

Great answer.
How sad that this forum requires so many things to give you an upvote for this.

Great simple answer !

I agree, but don’t yet have the points to do it.

You can use conditional formatting. Select the relevant cells and choose Format > Conditional Formatting > Conditional formatting.

That will only apply it to a single cell not to the entire row of cells where the value was found

It will apply to the selected range. Using references in ‘Formula is’ mode you need thoroughly distinguish between absolute and relative addressing. And you will have to give the proper references for the top left corner of the selected range you want to apply the CF to.

Sorry, but it actually DOES apply only to column A in my case, although I selected the range A2:Q104. My formula is $B2:B104=4 to check the value in cells B2:B104 and paint “green” every such row where $B=4.
What are the ways to make sure that ALL properties of column A apply to columns B:Q? For some reason only cells in column A get that condition applied to them.

$B2=4 used in a CF-condition for the CF-range A2:Q104 automatically checks for $B99=4 in row 99 (e.g.).
Fort a long time now CF-conditions in LibreOffice are to give for the topmost leftmost cell of the range for which they are created/edited.
The missing $ in the second part of the condition posted by @kostya_b was an additional error.

@PeterArt - use what @rveerd lined out and format the entire row with the same conditional format and cells in the row show the color you need.

That’s not practical i got 23 files with each about 3000 lines…

possible to set up one line then copy - select the 3000 - paste ??

If the documents/sheets are structured identically you may copy “formats only” using ‘Paste Special…’.

Best ‘Copy’ after ‘Edit’ > ‘Select All’.

‘Paste Special…’ formats only into the next document/sheet starting with A1.

The named cell styles will be CopyPasted as needed between documents.