We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Alternating colors for a selected regions with macro [closed]

asked 2013-04-05 12:58:01 +0200

student gravatar image

updated 2020-07-21 20:25:03 +0200

Alex Kemp gravatar image

I want to have alternating background colors on a region of a table in libreoffice calc. I know that there are multiple methods to do so, including conditional formatting and autoformat (each one has advantages and disadvantages) and last but not least writing a macro.

If I take the following macro from this answer it works fine except of three things:

  1. If you select a rectangular region and apply it, it nevertheless colors every second whole row (and not just the part in the region)
  2. If a cell is already colored, it doesn't overwrite the color.
  3. If you applied the macro to a region and you want to undo it, you have to undo every step of the loop. It would be better if on undo click would undo the whole alternating coloring process, i.e. the whole macro.

How can I modify the macro to make the points above work?

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
End Sub
edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-28 03:03:08.089171

1 Answer

Sort by » oldest newest most voted

answered 2013-04-06 00:44:38 +0200

m.a.riosv gravatar image
edit flag offensive delete link more


Works with current on Linux.

knezi gravatar imageknezi ( 2016-01-23 14:31:43 +0200 )edit

Question Tools


Asked: 2013-04-05 12:58:01 +0200

Seen: 368 times

Last updated: Apr 06 '13