| View previous topic :: View next topic |
| Author |
Message |
philb1
OOo Enthusiast
Joined: 21 Mar 2011
Posts: 111
Location: Auckland
|
 Posted: Wed May 11, 2011 2:13 am Post subject: Check either cell background or text colours |
 |
|
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 |
|
 |
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
|
| Back to top |
|
|
philb1
OOo Enthusiast
Joined: 21 Mar 2011
Posts: 111
Location: Auckland
|
| Posted: Wed May 11, 2011 12:34 pm Post subject: |
|
|
| 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 |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Wed May 11, 2011 6:56 pm Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu May 12, 2011 2:11 am Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
philb1
OOo Enthusiast
Joined: 21 Mar 2011
Posts: 111
Location: Auckland
|
| Posted: Thu May 12, 2011 11:50 pm Post subject: |
|
|
| 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 |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Fri May 13, 2011 3:08 am Post subject: |
|
|
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 |
|
|
philb1
OOo Enthusiast
Joined: 21 Mar 2011
Posts: 111
Location: Auckland
|
| Posted: Mon May 16, 2011 2:32 am Post subject: |
|
|
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 |
|
|
|
