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

Check either cell background or text colours

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
philb1
OOo Enthusiast
OOo Enthusiast


Joined: 21 Mar 2011
Posts: 111
Location: Auckland

PostPosted: Wed May 11, 2011 2:13 am    Post subject: Check either cell background or text colours Reply with quote

Hello Everyone

Is there a way to test for a cell background or data input colour? It doesn't matter which. All my cell backgrounds are a light blue colour by default because I hate a white screen glaring at me. So I was thinking it's better to test for data input (character) colouring
If A1=Red cell background or character colour & C1=.5 carry out a formula, or if A1=Green & C1=1 carry out a formula otherwise do nothing
It's the testing for colours that's got me

Thanks
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Wed May 11, 2011 5:34 am    Post subject: Reply with quote

I think yo can only do this using macros. This thread may help:

http://www.oooforum.org/forum/viewtopic.phtml?t=33925
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
philb1
OOo Enthusiast
OOo Enthusiast


Joined: 21 Mar 2011
Posts: 111
Location: Auckland

PostPosted: Wed May 11, 2011 12:34 pm    Post subject: Reply with quote

Hi Robert.. When I was searching for an answer & before I posted here,I saw that thread. I did see in a few other places that calc can't do that through formulas. That was older versions of ooo they were referring too, I thought that may have been included now. I'll keep trying :-)
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Wed May 11, 2011 6:56 pm    Post subject: Reply with quote

Although Calc can't do this with a standard formula it can do it with a user defined function.

In this case A1 contains a number and the font color is set to the first red available in the font color picker. C1 contain 1 or something else. The formula entered wherever is =CELLRED("A1") or as you will see later it could be =CELLRED(B1). First the function, which will either add A1 and C1 and return the sum or return "Do nothing".
Code:
Function CellRed(cell as String)
oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = oSheet.getCellRangeByName(cell)
cCell = oSheet.getCellByPosition(oCell.CellAddress.Column+2,oCell.CellAddress.Row)
If oCell.CharColor = 16711680 And cCell.Value = 1 then
  'Do something
  CellRed = oCell.Value + cCell.Value
 Else CellRed = "Do nothing"
EndIf
End Function


Assuming that column A is full of numbers which are either red or some other color and column C contains 1 or something else then you would want to be able to copy the formula done some other column. In this case enter A1 in cell B1 and drag down so it changes to A2, etc. Now you would use the =CELLRED(B1) formula.

If you don't know how to deal with macro code like this see this link.
http://www.oooforum.org/forum/viewtopic.phtml?t=99057
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu May 12, 2011 2:11 am    Post subject: Reply with quote

Quote:
ThisComponent.CurrentController.ActiveSheet

This does not work. Select any other sheet, perform a hard recalculation [Ctrl+Shift+F9] and see what I mean. Same issue when the hard recalculation happens on file open.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
philb1
OOo Enthusiast
OOo Enthusiast


Joined: 21 Mar 2011
Posts: 111
Location: Auckland

PostPosted: Thu May 12, 2011 11:50 pm    Post subject: Reply with quote

I presume it's a no no. I might try a check box. But only if I can figure out how to get the damn thing to bind to a cell lol
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri May 13, 2011 3:08 am    Post subject: Reply with quote

Hi Phil,
Villeroy's macros work, although as far as I can see they don't automatically update.
One way of getting a cell with the CELL_CHARCOLOR function to automatically update is to include +RAND()*0 in the cell formula. Cells with the RAND function are recalculated when any format change is made. Since RAND()*0 is always zero its addition to the recalculated CELL_CHARCOLOR function has no effect on its new value.
The attached doc's Standard library has 3 of Villeroy's functions...
CELL_CHARCOLOR
getSheetCell
getSheet
The formula you described is in E1:E6...
When A1's font colour is Light Red (16711680) (Red 8388608 is more like brown) and C1 is 0.5 A1+C1 is calculate; when A1's font colour is green (32768) A1-C1 is calculated; otherwise an empty string is returned.

In E9:E24 the CELL_CHARCOLOR function returns the value of the cell's current font colour's RGB value. (Odd that Light Green and Green 8 are actually the same colour)
http://www.mediafire.com/view/?ol6b78osdbirbe6
When the doc is opened you have to click the "Enable Macros" button.
If you don't see the dialog with this button then you will need to set your Macro Security level to Medium (Tools|Option|OpenOffice.org|Security|Macro Security|Medium).

While using the format of a cell to control a calculation might seem like a good idea it is always better to do the reverse, use a calculation to control a cell's format (Conditional Formatting).

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Thu Oct 04, 2012 7:35 pm; edited 1 time in total
Back to top
View user's profile Send private message
philb1
OOo Enthusiast
OOo Enthusiast


Joined: 21 Mar 2011
Posts: 111
Location: Auckland

PostPosted: Mon May 16, 2011 2:32 am    Post subject: Reply with quote

He JohnV, Robert, Villeroy & Ken.. Sorry for delay in getting back here. Been busy with house stuff while the weather's been kinder. I've had 2nd thoughts about doing this as I don't thinks it's going to work with the spreadsheet laid out as it is. It would mean I'd have to basically butcher it. So I've come up with another idea on another thread.

Thanks
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