Ask Your Question
0

How to alternate row background colors WITHOUT overriding conditional font color

asked 2017-04-11 22:39:53 +0200

neth gravatar image

I'd like to have a table in LibreOffice Calc 5.3.0 that has alternating row background colors, and at the same time highlights values below 5 with a red font color. Here's an example of how I want it to look:

expected result

But I want the styles to be fully dynamic, so that I can modify cell values and add/remove rows and the styles will update themselves accordingly.

From what I read, it sounds like I need conditional formats - but I can't get it to work correctly. Here is what I did:

1) I created two custom cell styles:

style 1 style 2

  • Style grey_background has no properties set except for a grey background.
  • Style red_font has no properties set except for a red font color.

2) I added two conditional formats to the cell range that contains the table:

  • One conditional format applies style grey_background based on the formula isodd(row()).
  • One conditional format applies style red_font if the cell value is less than 5.

But the result is wrong:

actual result

As you can see, the numbers 4 and 3 didn't turn red, even though they match the "less than 5" condition. It's as if the grey_background style is blocking the red_font style from being applied to the same cell.

What is happening there, and how can I fix it?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-04-12 08:38:26 +0200

pierre-yves samyn gravatar image

Hi

If I understand the question you only need a user-defined number format:

[>=5]Standard;[<5][RED]Standard;Standard

and only one conditional formating

ISEVEN(ROW(A1))

See CondFormat.ods (use Shift+Ctrl+F9 to test different values)

Regards

edit flag offensive delete link more

Comments

Oh, that's neat. Too bad it only supports these 8 colors though.

neth gravatar imageneth ( 2017-04-12 11:08:44 +0200 )edit

Yes you are right but IMHO in this context (font color) the number of shades is less important because more difficult to perceive.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-04-12 13:59:59 +0200 )edit
0

answered 2017-04-11 23:11:05 +0200

m.a.riosv gravatar image

You need one more format for the row with red background and red font.

ConditionFormat_rows+textcolor.ods

image description

edit flag offensive delete link more

Comments

So to be clear, there's no way to let two styles stack?

Manually creating additional styles for each combination isn't very scalable. Imagine if I'd want 10 different dynamic styles... instead of creating 10 Styles and 10 simple Conditional Formats, I'd have to create 1024 Styles and 1024 complex Conditional formats.

neth gravatar imageneth ( 2017-04-12 11:11:36 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-04-11 22:39:53 +0200

Seen: 5,796 times

Last updated: Apr 12 '17