Ask Your Question

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

asked 2012-12-19 20:31:30 +0200

lewwarren gravatar image

updated 2020-07-21 20:23:45 +0200

Alex Kemp gravatar image

Adding color to alternate rows of a table/spreadsheet

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 2015-11-14 12:24:54.030830

5 Answers

Sort by » oldest newest most voted

answered 2012-12-19 21:11:13 +0200

JohnSUN gravatar image

With Conditional Formatting.

Select range and apply format with formula




or both

image description

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.

image description

edit flag offensive delete link more


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

holy-harp gravatar imageholy-harp ( 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

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

answered 2013-11-12 15:07:37 +0200

Jim7fl gravatar image

updated 2013-11-12 15:11:22 +0200

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


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

edit flag offensive delete link more

answered 2012-12-25 11:48:52 +0200

snison gravatar image


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?


edit flag offensive delete link more

answered 2013-02-18 01:16:43 +0200

Edward gravatar image

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.

edit flag offensive delete link more

answered 2012-12-25 14:25:34 +0200

JohnSUN gravatar image

updated 2013-02-17 16:02:57 +0200

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("") Then
        oRows = oCurrentSelection.getRows()
        For i = 0 To oRows.getCount()-1 Step 2
            oRows.getByIndex(i).setPropertyValue("CellBackColor", nCellBackColor)
        Next i
End Sub
edit flag offensive delete link 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)
End Sub
Lua gravatar imageLua ( 2019-08-10 18:27:48 +0200 )edit


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)

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

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

estes53 gravatar imageestes53 ( 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).

jelabarre59 gravatar imagejelabarre59 ( 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?

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

Question Tools



Asked: 2012-12-19 20:31:30 +0200

Seen: 77,774 times

Last updated: Nov 12 '13