How do I determine what color a Calc cell is?

[MichaelFontaine]

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?

[Lupp]

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.

[MichaelFontaine]

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.

[sveinki]

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.

[mariosv]

Please, take a look in this thread.

Counting cells based on color

I have a calendar built, and I would like to be able to fill cells with a color and have a way to count the number of cells that have that color. So, in a defined range filled with, for example, red (representing vacation days) how can I sum all cells filled with red. This is helpful as I can slowly fill in all vacation days, holidays, then half or quarter vacation days, and quickly move them around and see where my total remains after different iterations. Thanks for your time.

[MichaelFontaine]

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

[PYS]

Hi

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:

Regards

[Lupp]

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

[inJesus]

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.

[Lupp]

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]

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.

[inJesus]

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?

[Lupp]

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]

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]

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

[inJesus]

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]

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)

[Lupp]

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]

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…

[Lupp]

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.