Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

Question

Besides using conditional formatting, is there any way in LibreOffice Calc (or OpenOffice Calc) to highlight every other row in order to make the spreadsheet easier to read?

LibreOffice has a serious bug that causes multiple errors when the typical conditional formatting technique ISEVEN(ROW()) is used to attempt this task and then rows are copied or moved. I found a LibreOffice bug report on this issue, but the bugs are still present.

Solution 1

I've written a macro in StarBasic back in 2004, which applies alternating colors to used cells (still working with LO 5.2.2.2 i use as of today). I hope the source is well documented to let you find the color definitions, if you like to change them ;-)

Copy the code to a module in your STANDARD library of Basic code, to be available to all CALC documetns. HTH

'Copyright (c) 2004, 2016 Winfried Rohr, re-Solutions Software Test Engineering

'This program is free software; you can redistribute it and/or modify it under 
'the terms of the GNU General Public License as published by the Free Software
'Foundation; either version 2 of the License, or (at your option) any later 
'version.

'This program is distributed in the hope that it will be useful, but WITHOUT ANY 
'WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 
'A PARTICULAR PURPOSE. See the GNU General Public License for more details.

'You should have received a copy of the GNU General Public License along with 
'this program; if not, write to the Free Software Foundation, Inc., 59 Temple 
'Place, Suite 330, Boston, MA 02111-1307 USA
' ========================================================================
Dim oDoc
Dim lRows as Long
Dim lCols as Long
Dim lStartRow as Long
Dim i as Long
Dim lEvenColor, lOddColor as Long
Dim sModulName, sModulSubName, sModulVersion

' -------------------------------------------------------------------
Sub colorCalcTableRowsEnglish ' manual extension 2006-03-24
sModulName = "wr CALC Modul"
sModulSubName = "colorCalcTableRows"
sModulVersion = "20040810"

oDoc = ThisComponent

If Not oDoc.supportsService(_
    "com.sun.star.sheet.SpreadsheetDocument" ) Then
        MsgBox _
        "Macro not called from CALC Document." & CHR(10) _
        & CHR(10) & "Explanation:" _
        & CHR(10) & "This Macro applies alternating, pre-definied" _
& CHR(10) & "background colors to the rows of the used cell"_
& CHR(10) & "range in CALC Documents and will only work there."_
& CHR(10) & CHR(10) _
& "Macro " & sModulSubName & " will terminate now." _
, 48 , sModulName & " " & sModulVersion
Exit Sub
End If

' RGB: Red/Green/Blue portion of color
' values could range from 0 to 255
' see Tools > OpenOffice.org > Colors for values
' 0,0,0: Black
' 255,255,255: White
' 
' Even/Odd correspond to ROW number
lEvenColor = RGB(255,200,200) ' kinda red
lOddColor =RGB(188,188,188) ' grey


if oDoc.Sheets.Count > 1 then
    ' more than 1 sheet, ask if macro should work on all sheets
    sQuestion = _
        "Applying alternating background colors to used cell range."_
        & CHR(10) _
        & CHR(10) & "Should all sheets be affected?" _
        & CHR(10) & "YES: apply on all sheets" _
        & CHR(10) & "No: apply to actual sheet only"

    iButton = _
        MsgBox(sQuestion ,35, sModulSubName & " - " & sModulVersion)

    Select Case iButton
        Case 2 ' cancel
            exit sub
        Case 6 ' yes = all sheets
            PROC_AllSheets
        Case 7 ' no = actual sheet only
            actSheet = oDoc.currentController.ActiveSheet
            PROC_colorSheetRow(actSheet)
    End Select
else
    ' only one sheet present
    actSheet = oDoc.currentController.ActiveSheet
    PROC_colorSheetRow(actSheet)
end if

End Sub

' -------------------------------------------------------------------
Sub PROC_allSheets

enumS = oDoc.getSheets.createEnumeration

While enumS.hasMoreElements
    actSheet = enumS.nextElement()
    PROC_colorSheetRow(actSheet)
Wend

End Sub

' -------------------------------------------------------------------
Sub PROC_colorSheetRow(actSheet)

lStartRow = 0
' watch out on first 4 rows if they might be formatted as heading
for i = 0 to 3
    ' don't touch rows with heading style
    oCell = actSheet.getCellByPosition(0,i)
    if INSTR(oCell.CellStyle , "Heading") > 0 then
        ' style heading found: increase start row
        lStartRow = i + 1
    end if
next i

' obtain last cell in sheet
vLastPos = FUNC_LastUsedCell(actSheet)
lRows = vLastPos(0)
lCols = vLastPos(1)

' if no more cell used - then nothing
if lRows = 0 AND lCols = 0 then
    exit sub
end if

' not more than headings
if lStartRow > lRows then
    exit sub
end if

' set range to one color (performance issue)
actRange = actSheet.getCellRangeByPosition(0,lStartRow,lCols,lRows)
actRange.setPropertyValue("CellBackColor", lEvenColor)

' now set color to Odd (number) rows (are even indexes)
for i = lStartRow to lRows
    ' determine range
    actRange = actSheet.getCellRangeByPosition(0,i,lCols,i)
    ' only every second row
    if((i MOD 2) = 0) then
        ' even index is odd row number
        actRange.setPropertyValue("CellBackColor", lOddColor)
    end if
next i
End Sub

' -------------------------------------------------------------------
' function uses variant array to return more than one value
Function FUNC_LastUsedCell(oSheet as Object) as Variant

oCursor = oSheet.createCursor()

oCursor.gotoEndOfUsedArea(TRUE) 
oEndAdr = oCursor.getRangeAddress

Dim vLastUsedCell(1) as Variant
vLastUsedCell(0) = oEndAdr.EndRow
vLastUsedCell(1) = oEndAdr.EndColumn 
FUNC_LastUsedCell = vLastUsedCell()

End Function

Solution 2

• From menu: Format → AutoFormat Styles.. . You could add your own.

  

• Another option is to use macros (as in ngulam's answer).

  Color2Rows is an extension that adds a button to the tool bar, for quick three color tables (head color plus 2 alternating colors over other rows). Source ask.libreoffice.org, tested still working in Version: 5.1.4.2

  There is another post on same site titled How do I make a button to apply a Table AutoFormat? but I don't know how to setup it to work.

Solution 3

The easiest way might just be to copy and paste direct formatting. It's somewhat tedious but doesn't take too long. I've tried several more automatic solutions that haven't worked as well.

Here are instructions for a three-row pattern; two rows would be similar. First, manually select and highlight the first three rows.

Then, select the first three rows and copy. Right-click on the fourth row and select Paste Special. Specify to paste only Formats.

Now 6 rows are formatted. Copy the 6 rows and paste 6 more so that 12 rows are highlighted. Then double it again for 24 rows, 48 rows, 96 rows and so on exponentially, untill all the rows are highlighted.

Comments

[RockPaperLz- Mask it or Casket]

Thanks Jim. Before I discovered conditional formatting, that's how I accomplished this goal. But then when you add/delete/move/copy rows, don't you windup with a mess and have to do it again?

[Jim K]

Yes, that is a drawback of this method. But it avoids the problems that can occur with conditional formatting. To make it easier to redo the formatting, you could keep a copy of the formatting on a separate sheet or document, and then copy it back when needed.

[RockPaperLz- Mask it or Casket]

From what I can tell, this will require Java (JRE) to be installed. Is that correct?

[RockPaperLz- Mask it or Casket]

Thank you. This is what I am doing now, as per your advice. The only downside is having to reapply the style every time a row is added or deleted. Any way around that?

[user.dz]

@RockPaperLizard, I have updated my answer, may be the only is go with macro solutions like ngulam's answer.

[ngulam]

No Java needed - this is StarBasic only code. For testing I disabled using a JRE (Tools>Options...>Advanced) and the macro is still running.

[RockPaperLz- Mask it or Casket]

Thanks for the added info. I will research and experiment!

[RockPaperLz- Mask it or Casket]

Wow! I got it to work! LibreOffice does this annoying thing where it insists on JRE even for BASIC macros. But you can just ignore the warnings, and it works wonderfully. Thank you!

[ngulam]

Great! If you'd like to change the colors used, look into Tools>Options...>LibreOffice>Colors, pick one from the color table and read the R/G/B values from the right side. Then change the values for lEvenColor or lOddColor accordingly. Have fun!

[RockPaperLz- Mask it or Casket]

Are you having success with this still working under LO 6.0.2.1? The macro seems to have completely disappeared for me when I "upgraded".

[RockPaperLz- Mask it or Casket]

Are you having success with the macro solution working under LO 6.0.2.1? The macro seems to have completely disappeared for me when I "upgraded".

[Andyc]

I get a BASIC runtime error "argument is not optional" an the line with oCursor = oSheet.createCursor() gets highlighted.