Ask Your Question
0

Get color of text in cell

asked 2018-05-07 14:40:19 +0200

editicalu gravatar image

updated 2018-05-07 14:42:49 +0200

I got a Calc file which contains data encoded in the color of cells (some cells are red and some are black. These cells indicate different values.

Is there a formula to get the color of a cell to extract this information?

A simplified example:

Simple example

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-05-07 15:42:16 +0200

Jim K gravatar image

updated 2018-05-07 15:42:47 +0200

Add this function adapted from https://ask.libreoffice.org/en/questi....

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.CharColor
    RGBarray(1) = Red(CBkC) : RGBarray(2) = Green(CBkC) : RGBarray(3) = Blue(CBkC)
    RGBprobe = RGBarray
End Function

Then enter =RGBPROBE(COLUMN()-1;ROW()) in cell C8 and drag to fill down.

This should only be a temporary solution. Once you have the values, put the data into another column, for example, "M" or "F". Then if you still want the colors, remove the hard formatting and apply conditional formatting based on the M/F column.

edit flag offensive delete link more

Comments

Thank you! Just wanted to add that I did not make this file, I just wanted to import the data.

editicalu gravatar imageediticalu ( 2018-05-07 16:43:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-07 14:40:19 +0200

Seen: 360 times

Last updated: May 07 '18