Ask Your Question

LO Calc: Set background based on formula [closed]

asked 2014-09-13 19:38:52 +0200

Ackbeet gravatar image

updated 2016-03-03 18:44:22 +0200

Alex Kemp gravatar image

Running LO 4.2 in English on Xubuntu 14.04 Linux in English, and I have a question concerning Calc. I have fine-tuned a formula that I want to use to set the background of cells: =AND(2<=MOD(ROW(),10),MOD(ROW(),10)<=6). If this formula is TRUE for a row, I want the row's background to be a light gray. If this formula is false for a row, I want the row's background unchanged. The result I want is that every five rows (corresponding to five workdays in a week) is "clumped" by color, and these colors will be (I hope) immune to cut-paste, unlike borders.

It seems to me that this sort of thing ought to be possible, but I'm just not seeing how to do it. If someone could please show me how to do this, I'd be much obliged.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-03 18:44:31.696013

1 Answer

Sort by » oldest newest most voted

answered 2014-09-13 20:08:26 +0200

karolus gravatar image

Select the Cellrange of Interest, and enter your Formula above into
→Format→Conditional Formatting.....Formula
Choose the Cellstyle with light gray Background for this.

edit flag offensive delete link more


The cellrange of interest is the entire spreadsheet. I'm sorry, but I can't follow your steps. From Conditional Formatting, where exactly do I go? Do I choose Condition, Color Scale, Data Bar, Icon Set, or Date? l'm guessing Condition. If I go to Condition, what precisely should be the settings? For the first setting, e.g., should I pick All Cells, Cell Value Is, Formula Is, or Date Is? I need all these details, please!

Ackbeet gravatar imageAckbeet ( 2014-09-13 20:22:01 +0200 )edit

Ok, more precisly:
→Format→Conditional Formatting→Condition.→Formula ...=AND(2<=MOD(ROW(),10),MOD(ROW(),10)<=6)
→→→your Style with grey Background

and for me it looks like a very bad Idea to format the whole sheet with 1024 Columns * 1,018,576 Rows in this way

karolus gravatar imagekarolus ( 2014-09-13 21:32:41 +0200 )edit

Ok. I was finally able to get the formatting to look right. The problem is, it doesn't behave right. If I cut the contents of one cell, and paste it in another, the formatting of the original cell is lost. How can I maintain the original formatting, without copying it to the destination cell?

Ackbeet gravatar imageAckbeet ( 2014-09-13 23:58:02 +0200 )edit

Question Tools

1 follower


Asked: 2014-09-13 19:38:52 +0200

Seen: 766 times

Last updated: Sep 14 '14