OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Detecting Cell Format

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Philip Barnes
Newbie
Newbie


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

PostPosted: Fri May 06, 2005 8:41 am    Post subject: Detecting Cell Format Reply with quote

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
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


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

PostPosted: Fri May 06, 2005 12:35 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri May 06, 2005 3:08 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


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

PostPosted: Fri May 06, 2005 4:55 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri May 06, 2005 7:41 pm    Post subject: Reply with quote

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


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.
Back to top
View user's profile Send private message
BookLady
Super User
Super User


Joined: 03 Nov 2004
Posts: 727

PostPosted: Sun May 08, 2005 9:51 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


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

PostPosted: Fri May 13, 2005 1:31 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri May 13, 2005 5:14 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


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

PostPosted: Fri May 13, 2005 4:16 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri May 13, 2005 4:55 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group