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 +0100

lewwarren 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 +0100

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 +0100 )edit

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

Jim7fl gravatar image

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

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 +0100

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 +0100

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 +0100

JohnSUN gravatar image

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

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 +0100 )edit

Question Tools



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

Seen: 65,363 times

Last updated: Nov 12 '13