Detecting Cell Format

Philip Barnes · Posted: Fri May 06, 2005 8:41 am Post subject: Detecting Cell Format

Hi
I am a membership secretary for a local hiking club and I have just recieved my first membership update by email.

Different statuses of the records has been highlighted in various colors, like a sheet of paper marked with a highlight pen.

Is there a way of detecting the cell color, such as if(cellcolor=green;do this;do that)

Thanks Phil

dfrench · Posted: Fri May 06, 2005 12:35 pm Post subject:

Not in calc.
There is a property available to you in macro coding and you can test that and do whatever you want to the sheet cells. For example, the following code works through the cells A1:A11 and inserts a value corresponnding to the background colour of the cell into B1:B11 (255 = blue, 65280 = green, 16711680= red , -1 = none but your shades may vary!)

David · Posted: Fri May 06, 2005 3:08 pm Post subject:

dfrench · Posted: Fri May 06, 2005 4:55 pm Post subject:

Not dumb at all. Smile

From the HELP ...
COLOR
Returns 1, if negative values have been formatted in color, otherwise 0.

It refers to the numeric formatting only, regardless of actual value being negative or not

David · Posted: Fri May 06, 2005 7:41 pm Post subject:

BookLady · Super User Joined: 03 Nov 2004 Posts: 727

Just trying to be sure I have this clear.

First part is that it is necessary to figure out what the numeric code or value is for the actual color of the cell and then work with the number of the value rather than the word word for the color.

Then the second part is that the word color in the phrase Cell("color";A1) is referring to the format which tells Calc to display negative numbers in color. This seems pretty pointless so maybe that isn't really what it does.

What say dfrench, have I got this right?

dfrench · Posted: Fri May 13, 2005 1:31 am Post subject:

That's right !
It is a long integer representing the colour as a combination of red,green,blue with the value of 0-255 each

David · Posted: Fri May 13, 2005 5:14 am Post subject:

dfrench · Posted: Fri May 13, 2005 4:16 pm Post subject:

First read my earlier post in this thread with the macro.

You may be confusing numeric formatting (Format Cells Number ..) with Font and Background. The Spreadsheet Function Cell("Color";A1) is about numeric formatting .
If you set a cell (say C11) to have the format

[$$-1409]#,##0;[RED]-[$$-1409]#,##0

then =CELL("color";C11) will return the value 1

this function is affected by an old bug, in 1.1.2 at least, and does not get alerted to the change of format of the target cell

David · Posted: Fri May 13, 2005 4:55 pm Post subject: