# Is there a way to add background color to alternate rows of a table/spreadsheet? [closed]

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-14 12:24:54.030830

Sort by » oldest newest most voted

With Conditional Formatting.

Select range and apply format with formula

ISODD(ROW())


or

ISEVEN(ROW())


or both

With own Autoformat.

Colorize rows as you want in range 5x5 cells. Select it. Choose Format-Autoformat. Press Add and set name. Then select range for formating and apply this autoformat.

more

"Colorize Rows" means to manually add the background color you want. Do not use ISODD(ROW()) conditional format on your sample format cells.

( 2017-11-03 09:26:19 +0200 )edit

This worked fine for me except only in empty cells. Cells with data still have a white background. Can't seem to find what is overriding it. Any suggestions? Thanx, Alan

( 2020-06-09 15:33:36 +0200 )edit

For this I generally have a sheet with named variables and other setup info which has a cell with the background I want and it is named, for instance, _blue

Then in Menu>Format>Conditional Formatting>Condition> click the "Cell value is" button and select "Formula is" put the following formula into the form: MOD(ROW()+1,2=1

In Apply Style select your named style

Click OK

Done

You can apply this to the whole sheet or any portion.

I like my financial sheets to have the first 4 or 5 rows as plain white for headings and such, but I like the rest looking like the old fashioned dot matrix continuous paper with the alternating light and dark backgrounds.

Hope this helps someone.

Best Regards,

Jim Smith

more

Hi:

When I use this and other methods found, specifically using ISEVEN(ROW()), it takes away the "center" formatting of the last column (F). When I try to correct this, it only changes the formatting for the even columns.

If I try to justify column C it also only does it for every even column.

How can I color alternate rows but keep formatting per column?

Thanks!

more

The AutoFormat feature allows you to select "more" options. Unchecking Font and Alignment should allow you to apply the autoformat while retaining the current font and alignment.

more

OK. Just select range and run this macro:

Sub ColorizeTable
Dim oCurrentSelection As Variant
Dim oRows As Variant
Const nCellBackColor = 15132415 REM # "Blue gray"
Dim i As Long
oCurrentSelection = ThisComponent.getCurrentSelection()
If oCurrentSelection.supportsService("com.sun.star.table.CellRange") Then
oRows = oCurrentSelection.getRows()
For i = 0 To oRows.getCount()-1 Step 2
oRows.getByIndex(i).setPropertyValue("CellBackColor", nCellBackColor)
Next i
EndIf
End Sub

more

@JohnSUN this works, but it colors entire row instead of selected one. The code below works for the latter purpose, please let me know if there is a better way to do it.

Sub ColorizeTable_2()
Dim nCol As Long 'Column index variable
Dim nRow As Long 'Row index variable
Dim oCols   'Columns in the selected range
Dim oRows   'Rows in the selected range
Dim oRange
Const nCellBackColor = 15132415 REM # "Blue gray"
oRange = ThisComponent.getCurrentSelection()
oCols = oRange.Columns : oRows = oRange.Rows
For nCol = 0 To oCols.getCount() - 1
For nRow = 0 To oRows.getCount() - 1 Step 2
oRange.getCellByPosition(nCol, nRow).setPropertyValue("CellBackColor", nCellBackColor)
Next
Next
End Sub

( 2019-08-10 18:27:48 +0200 )edit
1

Hi,

Why is this feature so difficult??

Im not trolling, im frustrated. I am a simple guy who is a brick layer in real life. I don't want to be a computer tech! All as I am trying to do is help my daughter young with her homework.

Not only do I have no clue to all that code I really don't want to know or care.

I simply want one line white and another green or whatever so we can see the lines with ease in table view. Like it is in Excl.

This is like word requiring a computing degree to change the font?? I guess its something else I will have to just do without. Surely the ability to read the lines and lines of data without a physical ruler deserves its own button? Or do you lot just like typing in loads of code ...(more)

( 2020-01-16 12:48:22 +0200 )edit

Because it is free. If you want all the neat features included pay for excel.

( 2020-06-09 15:29:23 +0200 )edit

Well, THAT was an obnoxious answer. The thing is, I remember LibreOffice used to have the bog-standard alternating bars as a built-in AutoFormat. Really can't understand why it was taken out. Doesn't matter if it's a "free" offering or some overpriced clunker like Excel; the format used to be there, now it isn't.

That said, it would be handy to have AutoFormat templates as installable templates. That was what I had been searching for (and don't seem to exist).

( 2021-05-07 02:24:16 +0200 )edit

@jelabarre59 Sorry, I didn't understand your comment. Didn't you find this command in the menu?

( 2021-05-07 07:06:24 +0200 )edit