quick way applying styles?

Hi,

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?

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

ColumnColours.ods

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.

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.

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.

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 …

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

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.

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

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

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 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!

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.