Ask Your Question

quick way applying styles? [closed]

asked 2015-04-16 01:49:09 +0200

LibreGuy gravatar image

updated 2020-07-22 08:23:23 +0200

Alex Kemp gravatar image


I will try to explain what I want to achieve. In Calc I would like to use 2 different styles for columns. The "first" column must have style 1, the "second" column style 2. So the first column has style 1, the second has style 2. Then the third column has style 1 again and the fourth column must have style 2, and so on. So let's pretend style 1 has a blue background and style 2 has a red background, then we would see a blue column, then a red column, a blue column and again a red column, a blue column and a red one, blue ... red ... blue ... red. Well, you get the picture by now I think. Now here's the deal. I want this "pattern" to repeat until the end of the spreadsheet. Yes ... of course I could try to manually select all of the rows, but I'm afraid if I do that I will end up insane. So question is, is there some sort of "trick" I could use to easily apply these styles?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-22 08:24:24.824145

2 Answers

Sort by » oldest newest most voted

answered 2015-04-16 02:17:21 +0200

Rugslug gravatar image

updated 2015-04-17 00:29:24 +0200

Version 1:

Cell A1=TRUE (literally type in the text TRUE)(boolean) Set two conditional formats and styles for the cell A1 = red background if TRUE; A1 = blue if FALSE (applies to the set of cells A1:Z100 or whatever) Copy A1 to A2 (include formats)

Cell A2=NOT(A1) Drag A2 across the top. Formula result should be alternating TRUE/FALSE and the coloured columns. Change A1 to FALSE and they should switch.

UPDATE1: attached a file with formulae and conditional formatting


UPDATE2 after suggestion from LibreGuy, I've updated the attached spreadsheet as well.

Instead of formula in the cells, only the conditional formatting is set with a Formula:

Condition1: IF(COLUMN()/2=INT(COLUMN()/2))

Condition2: IF(COLUMN()/2<>INT(COLUMN()/2))

The two formulae above determine if the column is an even or odd number. The result is then applied to a set of cells.

One could do the same with rows.

edit flag offensive delete link more


Thank you, I'm not sure if I totally understand what you mean. If I do this, will I be applying a formula to each cell? I do not want to use formulas for styling so that's why I'm asking. I don't want some sort of dynamic response. I want to apply the styles one time only (just as if I had manually applied them), and that should be it.

LibreGuy gravatar imageLibreGuy ( 2015-04-16 02:46:02 +0200 )edit

There will be a formula in one row, all the cells in that one row.

You could try recording a macro of the manual setting of the colours.

Rugslug gravatar imageRugslug ( 2015-04-16 03:17:24 +0200 )edit

But I don't want to have formulas so I think that's not an option then. I just want to have the styles applied like they would be when I do it manually. But doing it manually is not an easy job ... UPDATE: conditional formatting seems to work without any formulas at all ...

LibreGuy gravatar imageLibreGuy ( 2015-04-16 04:01:14 +0200 )edit

Nice. I've updated the ColumnColours.ods file in my original post to show two different ways to set the column colours.

Rugslug gravatar imageRugslug ( 2015-04-17 00:19:35 +0200 )edit

Sorry for my late response ... What you are doing can be done a lot easier. Just use "formula" and then use ISEVEN(COLUMN()) or ISODD(COLUMN()). Give it a try and let me know if it worked.

LibreGuy gravatar imageLibreGuy ( 2015-04-18 04:01:47 +0200 )edit

No prob. I did use formula. Didn't know ISEVEN/ISODD function. Love that one. Saves some calculating.

Rugslug gravatar imageRugslug ( 2015-04-18 22:52:42 +0200 )edit

answered 2015-04-18 13:01:12 +0200

Lupp gravatar image

updated 2015-04-18 13:02:45 +0200

You may have a look into another attached example: ask49356ConditionallyColouredColumns001.ods. It also contains an image of the dialogue window you get by 'Format' > 'Conditional Formatting' > 'Manage' > select the only listed range > 'Edit'.

edit flag offensive delete link more


Isn't that the same as I was saying (in response to Rugslug)? However, instead of ISODD(COLUMN(A$1)) I did this ISODD(COLUMN()). I left out the A$1 cell. I don't know why you put that in there?

LibreGuy gravatar imageLibreGuy ( 2015-04-18 16:04:53 +0200 )edit

@LibreGuy The only "enhancement" as compared to the answer by @Rugslug I intended was the dialogue image. Using formulae on the other hand is the main principle of spreadsheets. There will never be a special tool for colouring sheets in stripes I suppose. If you insist to have one you will have to write a Sub. Now and then you will insert or delete a column ... The direct formatting will get in conflict with styles to apply ... Good luck!

Lupp gravatar imageLupp ( 2015-04-18 22:35:26 +0200 )edit

I was talking about formulas in the cells itself. Now the formulas are in the conditional formatting settings, so no problem there. The colouring in stripes works just fine using conditional formatting. Using a sub/macro is not necessary at all.

LibreGuy gravatar imageLibreGuy ( 2015-04-19 15:53:28 +0200 )edit

Question Tools

1 follower


Asked: 2015-04-16 01:49:09 +0200

Seen: 217 times

Last updated: Apr 18 '15