Ask Your Question

How do I determine what color a Calc cell is?

asked 2015-05-01 18:23:17 +0100

Michael Fontaine gravatar image

I created a spreadsheet and am trying to continue the color scheme I was using, but I can't figure out what color I was using for a particular cell. If I look at the cell properties it shows me the actual color, but doesn't tell me what it's name is or otherwise indicate what the color is. I can't seem to find this color in the palette provided when I try to set the background color of a cell, and it isn't showing it in previously used colors (not sure it was created on the same machine or version of LibreOffice.)

How can I determine the color I used so I can re-use it in other places?

edit retag flag offensive close merge delete


A color scheme must consist of named styles. Naming of colors by the user is local and volatile. Styles and their names are stored with the document.

Lupp gravatar imageLupp ( 2017-12-03 01:44:38 +0100 )edit

7 Answers

Sort by » oldest newest most voted

answered 2015-05-15 23:34:21 +0100

Michael Fontaine gravatar image

Although it won't tell you what the actual color is, the easiest solution appears to be to copy a cell that has the desired color, then select the cells you want to give that color, and then do Edit > Paste Special > "Formats", making sure no other checkboxes are selected (normally this requires de-selecting a few of them). Unfortunately, if there is any other formatting such as number formatting or borders that will have to be re-done, but in my case I merely needed to set the number format correct again, and everything was back to a uniform color.

edit flag offensive delete link more

answered 2017-12-03 02:34:07 +0100

librebel gravatar image

Hello @Michael Fontaine, 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

edit flag offensive delete link more

answered 2017-12-04 16:03:11 +0100

sveinki gravatar image

Tested in LO 5.4.3 on LinuxMint: select colored cell (the color you want to know more about) and open the 'Background' dialog. You should see your color in the 'Recent colors' if it has been applied before, and there should be a frame around the swatch if it is in the chosen palette. Hovering with the cursor over it gives you its name, but that's only 'Red_3' or some generic name.

Now, if you don't touch anything in this dialog, but choose 'Custom color...' at the bottom, a color-selector window should open, already pre-filled with information about the color of the cell; in RGB, HSB and CMYK !

This was not working for me in LO 5.2, the color-selector was at some irrelevant values when opened.

To apply a consistent coloring scheme in a document, a good way is to choose document colors as a palette, just like @Hopeful-LO-user pointed out.

edit flag offensive delete link more

answered 2015-05-16 07:43:02 +0100

pierre-yves samyn gravatar image


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:



edit flag offensive delete link more

answered 2015-05-16 13:18:09 +0100

Lupp gravatar image

updated 2015-05-16 13:22:02 +0100

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

edit flag offensive delete link more


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.

inJesus gravatar imageinJesus ( 2017-05-11 22:04:51 +0100 )edit

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.

Lupp gravatar imageLupp ( 2017-05-11 22:23:46 +0100 )edit

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.

Lupp gravatar imageLupp ( 2017-05-11 22:29:30 +0100 )edit

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?

inJesus gravatar imageinJesus ( 2017-05-11 22:38:40 +0100 )edit

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.

Lupp gravatar imageLupp ( 2017-05-11 22:54:06 +0100 )edit

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...

Lupp gravatar imageLupp ( 2017-05-11 23:04:30 +0100 )edit

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).

Lupp gravatar imageLupp ( 2017-05-11 23:12:02 +0100 )edit

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.

inJesus gravatar imageinJesus ( 2017-05-12 00:38:39 +0100 )edit

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. (The font color of the hypertext text is different than the normal text)

inJesus gravatar imageinJesus ( 2017-05-12 00:43:10 +0100 )edit

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?

Lupp gravatar imageLupp ( 2017-05-12 00:47:49 +0100 )edit

answered 2017-12-03 00:49:36 +0100

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.)

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2017-12-03 01:41:07 +0100 )edit

answered 2015-05-01 22:02:42 +0100

m.a.riosv gravatar image

Please, take a look in this thread.

edit flag offensive delete link more


so... requires some knowledge of macros and can't be done directly through the UI then?

Michael Fontaine gravatar imageMichael Fontaine ( 2015-05-15 23:31:53 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-05-01 18:23:17 +0100

Seen: 8,516 times

Last updated: Dec 04 '17