I have and OpenOffice Calc document that has a "table" with Text, Numeric and Date columns. I have a conditional format making every odd row have a different background color so it will be easier to read.

The problem is that the rows which are affected by the conditional format will loose it's other format. In this case the date. If I put a date format in the conditional style then the Numeric cells will become a date...

Is this fixable and if so How?

2 Answers 2


Select the range and run this macro:

Sub Main
    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
  • 1
    Very nice - works for LibreOffice Calc, too!
    – tohuwawohu
    Jul 8, 2015 at 18:28

You could try to use an extension rather than conditional formatting, this shouldn't change the formatting of the cells (other than the background)


  • Since the extension hasn't been updated since 2009, I didn't try it. Maybe it works...
    – Andis59
    Jul 8, 2015 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.