[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Philip Barnes
Newbie

Joined: 06 May 2005
Posts: 3
Location: Leicestershire, UK

 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
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

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!)
 Code: Sub Main oSheet=Thiscomponent.sheets.getbyindex(0) 'first sheet icol=0 irow=0 for irow=0 to 10 oCell=oSheet.getcellbyposition(icol,irow)    oSheet.getcellbyposition(icol+1,irow).value= ocell.cellbackcolor next End Sub
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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

 dfrench wrote: Not in calc.

Now I have a dumb question. What does Cell("color";A1) return? I get zero, no matter the background color, so what is color referring to please?

David.
dfrench
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

 Posted: Fri May 06, 2005 4:55 pm    Post subject: Not dumb at all. 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
Super User

Joined: 24 Oct 2003
Posts: 5668

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

 dfrench wrote: Not dumb at all. 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

Sounds like quirks and quarks to me [nuclear physics], you'd think that there would be some real relationship with "color" [aka "colour"].

Thanks .

David.
Super User

Joined: 03 Nov 2004
Posts: 727

 Posted: Sun May 08, 2005 9:51 am    Post subject: 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
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

 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
Super User

Joined: 24 Oct 2003
Posts: 5668

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

 dfrench wrote: 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

Using 1.1.3 [sorry], I get a return of 0 [zero] no matter how I format the cell background or font. I've tried all combos I can think of, with background colour, font colour, negatives and positives. All I see is a zero return.

Is the long integer value used as a comparitor for a return of zero or one? What must I do to see other than zero please?

David.
dfrench
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

 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
Super User

Joined: 24 Oct 2003
Posts: 5668

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

 dfrench wrote: 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

Thanks.

David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum