Ask Your Question

Is it possible to check/test a cell if its background is other than default?

asked 2018-04-13 15:56:08 +0200

mzcl-mn gravatar image

Hello :)

I think the question is self-explanatory.

Nevertheless I will try to explain myself better.

I am looking for a way to fill a cell's value if its background color (which is the same as the row's background color) has changed from default value into a specific palette color.

Is this possible?

How quick should it be?

Thanks in advance!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-04-13 18:37:34 +0200

mzcl-mn gravatar image


I do appreciate your answer... I think what it means is that ... I should just redo all the work... :( And that is a little frustrating... but...


Thanks anyway...

edit flag offensive delete link more


If you started with coding information by colors, you can use functions lik the ones I posted to work with that information. My advice is, to do that once and for all by transforming that information to cell contents. Use a dedicated column, and place there an IF() formula based on the cellBackColor function. Then copy the results and Paste Special... the values only replacing the formulas. Now you can refer to the results wherever you intended to refer to the color attributes.

Lupp gravatar imageLupp ( 2018-04-13 19:15:51 +0200 )edit

answered 2018-04-13 16:48:58 +0200

Lupp gravatar image

updated 2018-04-13 16:51:38 +0200

-1- Palettes change with time. It might be useless to try to find ou if a cells has background color 'Red2'.
-2- Next to always attributes of the kind 'background color' or 'font color' should first be assigned to named cell styles which then can be
-2a- directly assigned to cells or
-2b- overlaid with thier attributes to cells by conditional formatting.

A formatting attribute may be used to display whether or not specific conditions are met. It should never be used to code for a relevant information. Data must be cell contents to get reliable functionality. And as soon as the data are available ... See -2b-!

Regarding these facts Calc does not offer means like standard functions for introspection into cell attributes.

If you have sheets wrongly using colors to code for information, and you want to rectify them by making that information explicit in dedicated cells, you need to resort to custom code. An example for functions helping with cell introspection written in LibreOffice BASIC and making API calls you find below.

Function cellBackColor(pZ As Long, pCellAddress As String, Optional pDummy) As Long 
'pDummy shall only provide a way to trigger execution depending on any reference or volatile function.'
    Dim theCell As Object, bckColor As Long
On Error GoTo errorExit
theCell = ThisComponent.Sheets(pZ - 1).GetCellRangeByName(pCellAddress)
bckColor = theCell.CellBackColor
cellBackColor = bckColor
End Function 

Function cellBackRGB(pZ As Long, pCellAddress As String, Optional pDummy) As String
'pDummy shall only provide a way to trigger execution depending on any reference or volatile function.'
Dim bckColor As Long, h As String
On Error GoTo errorExit
bckColor = cellBackColor(pZ, pCellAddress)
h = "(R,G,B) = (" & Red(bckColor) & "," & Green(bckColor) & "," & Blue(bckColor) & ")"
cellBackRGB = h
End Function
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-13 15:56:08 +0200

Seen: 45 times

Last updated: Apr 13 '18