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:
- If you select a rectangular region and apply it, it nevertheless colors every second whole row (and not just the part in the region)
- If a cell is already colored, it doesn’t overwrite the color.
- 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
EndIf
End Sub