How do I determine what color a Calc cell is?


If you have not unchecked Tools Options LibreOffice General Help Tips, just let the mouse in the palette on the color to see its name, e.g. Chart 3 in this example:



If the colours you want to use were defined in an old palette (or by the user) and the then used names do no longer exist for your present user profile the colour itself will still be shown but you don’t get information about it via the dialogue. I think LibreOffice should offer a way to define a new name for an unnamed colour occurring in a document, but the palette might tend to grow rather uncontrolled this way.

To avoid persisting problems I would suggest: (1) Explicitly define your colour scheme by examples. (2) Choose named colours very similar to the original ones. (3) Apply them to the named cell styles you want to use with them. (4) If you missed to organise your colour scheme based on named styles, this might be a good opportunity to catch up to that more professional method and hence avoid direct formatting with fewer exceptions.

If you still want to explore the old colours you may use the function code (in BASIC) given below. The function used as an array expression will output the R, G, and B values in three adjacent cells of a row. You may then use this value to add a new named colour from this RGB triple to your palette. Be aware, please, of the fact that a document opened with another LibreOffice will again not contain these colour names.

Function RGBprobe(x , y, optional z)
Dim RGBarray(1 to 3)
oDoc = ThisComponent
oSheet = oDoc.Sheets(0)
'Decreasing coordinate values by 1 because BASIC starts numbering with 0.
If NOT IsMissing(z) Then oSheet = oDoc.Sheets(z-1)
oCell = oSheet.getCellByPosition(x-1,y-1)
CBkC = oCell.CellBackColor
RGBarray(1) = Red(CBkC) : RGBarray(2) = Green(CBkC) : RGBarray(3) = Blue(CBkC)
RGBprobe =RGBarray
End Function

I attach a document demonstrating how to use the function: RGBtest001.ods

I downloaded your file. =RGBPROBE(COLUMN(C4),ROW(C4),1) gives an 0 for a cell with blue background. With { } also not. I would also be good to be able to copy the formula to try it.

When running the macro I get an error on
oCell = oSheet.getCellByPosition(x-1,y-1)
Basic runtine error. Argument is not optional.

Of course, you cannot run a Function using parameters as a Sub. Call =RGBprobe(Column(Z6);Row(Z6);Sheet(Z6) from a Calc cell and you will get the RGB triple. You can also ccall the function from a Sub if you supply it with the appropriate parameter values.

The function is designed to return (R, G, B) as a row-like triple of values. Called from Calc it must therefore be allowed to lock three adjacent cells in a row for its output. Of course you can rewrite it to concatenate the triple to a text for output in one cell.

Make sure to understand that the usage of introspective functions of the kind for coing actual data in format properies is a grave mistake.

Also note that conditionally applied formats cannot be evaluated this way at all.

How to do this correctly? Would be happy if you give me the complete solution. From this I can learn best also. And what is coing?

How to call a function from a cell?

Concerning my warnings I cannot give you “a complete solution”. You will need to understand them.
How to call the function from a cell is demonstrated in the example attached to my answer above.
The mentioned alternative is to replace the final two lines of the function body by
RGBprobe ="(R="+Red(CBkC)+",G="+Green(CBkC)+",B="+Blue(CBkC)+")"RGBarray
Omit Dim RGBarray(1 to 3) in this case.

As you see I have some experience with forums on “our” software. In addition I am an old teacher. It is a well backed experience that questions often miss to exhibit the actual goals, and only display a problem with a step needed for a proposed solution. An experienced user might know a better one avoiding the “problem”. In short for this example: I cannot think of a reasonable spreadsheet design actually needing to read the BckColor from cells. The question comes again and again…

Red(), Green() and Blue() are predefined functions extracting the respective component values in the range of 0…255 (‘Byte’ type) from a 24-bit RGB value. The reverse function is RGB(RedByte,GreenByte,BlueByte).

Does call a function mean the following? Define a function in a macro. Trigger it by entering it into a cell. ?? Many thing you explained in detail I understand already before you explained it. But this is what is missing. Also what exactly must it add in your macro to not have the mentioned Runtime error? Or what else to do exactly.

I love to tell you my goal: My goal is to have 500 normal text (non hypertext) entries deleted of a column with bookmarks (hypertext entries) - see here, also an example file. How to remove non hypertext from a list of bookmarks (automatically)?. (The font color of the hypertext text is different than the normal text)

To “call” a function is to use it in an expression. To call a sub ist to make it run.
There is nothing to add in “my” function. It simply needs values for its parameters. In other words: it must be called with actual parameter expressions in the places of the formal parameters. How should it know otherwise for which one of 2^30 cells it should look.
BTW: I honestly expect you to also read my side advices. What do you think to need the RGB for?

Did you open the RGBtest001.ods attached to my answer. If so you see how it is applied.
If you also use the BASIC IDE you can execute code step by step.
To look into a Function executed stepwise, automatic debugging must be active - or you have to call the function with the help of a Sub containing an extra line of code providing the parameter values…

That’s about what I expected. And: You are wasting time (yours and mine). What you try will never work. I already told you that “format properties conditionally applied” cannot be retrieved by a function like the one I posted. They simmply are not accessible as cell properties. With the colors hyperlinked text is displayed in it’s the same. (This is a different case.)
To find out if a cell is containing a hyperlink you have to use different means.

What is an expression? Is this a formula? Dear Lupp, sorry I would be more helped if you give me one solution (the best :-)) to try first, not also 2 or 3 alternatives. And this one solution I need to understand. And just get an example maybe, an ods for instance where this is done. I feel treated unfair by you. To understand what I want you can ask me and I will answer. And I appreciate if you do the same. You explain just to complicated and too much different things from my point of view.

I also have the impression that I have already given clear answers and you have not read them.
And you advice me some things I have already done and written to you also, like been trying with your example file. Honestly I am frustrated now with your reactions.

Two work-arounds I have found. (I presently run LibreOffice (x64) on Windows 10 64-bit. I have not tested other versions or platforms, so your results may vary.)

The first method won’t give you the colour code, but it should let you set the background colour to the one you want without disturbing anything else, assuming it is already used in the document. Select the cell you want to colour. Open the Background dialog. There are several available palettes. Click the down arrow and select the “Document colors” palette. This palette appears to show only the colours in use in the current document (may include colours no longer in use). From this (hopefully) short list, it is relatively easy to match the colour you want.

The second method depends on having specific additional software installed. Other programs may work similarly, but the only one I have found so far is Snagit (presently using Ver 11). In Snagit, pick an editing option that lets you choose the fill colour. Snagit lets you take the eyedropper cursor outside the program window. Hover over the cell whose background colour you wish to copy, and the Tooltip will give the RGB values. From there you can use the Custom Color option in the LO Background dialog to duplicate it. As for other programs, I know Windows Paint and The Gimp will not work. Snagit works on Windows and Mac only, but other screen capture programs may have a similar capability.

(Sorry I could not post screen shots - not enough karma.)

Did you consider that the palettes and the color names were often changed? The actual colors (RGB) assigned to any names are subject to changes by the developers and by the user as well. The color name originally displayed when a color value was set is not stored. And surely there aren’t names for everyone of the 2^24 colors of the currently used color space.

Hello @MichaelFontaine, and @Hopeful-LO-user

In addition to the macro given by @Lupp, which returns the RGB values of the Cell Background Color as an Array, the following macro returns the color’s RGB values as a single Long Integer, and the next following macro returns the color’s HEX values as a String.

Just call in your Calc cell: = getCellBackColorRGB(“A1”) or =getCellBackColorHEX( “A1” )

Function getCellBackColorRGB( strCellAddress$ ) As Long
REM <strCellAddress>:	pass a Cell address in the current Sheet, such as "A1" or "$A$1".
REM Returns a Long Integer representing the RGB values of the Background Color of the specified Cell.
	Dim oSheet As Object : oSheet = ThisComponent.CurrentController.ActiveSheet
	Dim oCell  As Object : oCell  = oSheet.getCellRangeByName( strCellAddress )
	getCellBackColorRGB  = oCell.CellBackColor
End Function

Function getCellBackColorHEX( strCellAddress$ ) As String
REM <strCellAddress>:	pass a Cell address in the current Sheet, such as "A1" or "$A$1".
REM Returns a String representing the HEX values of the Background Color of the specified Cell.
	Dim oSheet As Object : oSheet = ThisComponent.CurrentController.ActiveSheet
	Dim oCell  As Object : oCell  = oSheet.getCellRangeByName( strCellAddress )
	getCellBackColorHEX  = RGBtoHEX( oCell.CellBackColor )
End Function

Function RGBToHex( lRGB As Long ) As String
	RGBToHex = ByteToHex( Red( lRGB ) ) & ByteToHex( Green( lRGB ) ) & ByteToHex( Blue( lRGB ) )
End Function

Function ByteToHex( iByte As Integer ) As String
	Dim strHex As String	: strHex = Hex( iByte )
	If Len( strHex ) = 1 Then strHex = "0" & strHex
	ByteToHex = strHex
End Function

HTH, lib