Ask Your Question
2

Calc howto: conditional format a row based upon a column

asked 2013-08-29 10:43:16 +0100

PeterArt gravatar image

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)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2015-03-04 05:53:46 +0100

jarfil gravatar image

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.
edit flag offensive delete link more

Comments

Someone upvote this!

mr_fnord gravatar imagemr_fnord ( 2015-03-25 03:52:32 +0100 )edit

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

Rob Nichols gravatar imageRob Nichols ( 2015-07-26 14:28:49 +0100 )edit

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!

P.Krzyzkiewicz gravatar imageP.Krzyzkiewicz ( 2017-04-02 12:59:43 +0100 )edit

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

QPhysics137 gravatar imageQPhysics137 ( 2019-04-30 16:50:51 +0100 )edit
0

answered 2013-08-29 13:00:18 +0100

ROSt52 gravatar image

@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.

edit flag offensive delete link more

Comments

That's not practical i got 23 files with each about 3000 lines..

PeterArt gravatar imagePeterArt ( 2013-08-29 13:44:34 +0100 )edit

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

JohnD gravatar imageJohnD ( 2013-08-30 05:38:57 +0100 )edit

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.

Lupp gravatar imageLupp ( 2015-03-04 17:34:01 +0100 )edit
0

answered 2013-08-29 11:10:59 +0100

rveerd gravatar image

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

edit flag offensive delete link more

Comments

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

PeterArt gravatar imagePeterArt ( 2013-08-29 11:49:58 +0100 )edit

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.

Lupp gravatar imageLupp ( 2015-03-04 17:25:47 +0100 )edit

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.

kostya_b gravatar imagekostya_b ( 2018-04-26 14:00:13 +0100 )edit

$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.

Lupp gravatar imageLupp ( 2018-04-26 14:20:06 +0100 )edit
Login/Signup to Answer

Question Tools

3 followers

Stats

Asked: 2013-08-29 10:43:16 +0100

Seen: 23,056 times

Last updated: Mar 04 '15