Gnumeric / LO Calc - how to get the background color of a cell?

I have a large spreadsheet, the rows of which are colored according to a rule that cannot be removed from the available cell data - there is no such information. So I want to look at the color of the cell and make that value.

The table looks like this:

data|data| <-- background colored row in red
data|data| <-- background colored row in blue
data|data| <-- background colored row in yellow


and I want to create a new column that will have a value according to that color.
Excel / Gnumeric / LO Calc has a search function called information cell

and a "color" attribute that unfortunately does not return a useful value:

cell("color", A2)

returns 0 or 1 and this is just a function that offers a function similar to what I need.

How can I get the background color of a cell back in Gnumeric or LO Calc in any way - via a plugin or anything else?


source to share

1 answer

In LibreOffice Calc:

  • Go to Tools> Macros> Organize Macros> LibreOffice Basic>
    My Macros> Standard> Module1

  • Select "Home" and click "Change".

  • It will contain an empty three-line pattern:

    REM  *****  BASIC  *****
    Sub Main
    End Sub

    Add this after the template:

    Function bgcolor(c,r)
      Dim oDoc  As Object   ' define variables
      Dim oSheet As Object
      Dim oCell As Object
      oDoc  = ThisComponent
      oSheet= oDoc.getSheets().getByIndex(0)
      oCell = oSheet.getCellByPosition(c-1,r-1)
      bgcolor = oCell.CellBackColor
    End Function

    You should see something similar in the editor:

    BGColor Macro in LibreOffice Calc

  • Close the Organizer and you can now use the function like any other function:


bgcolor(3, CELL("Row",A1))
bgcolor(CELL("COL", A1), CELL("ROW", A1))




All Articles