Set cell colour based on other cell colour

Discuss the spreadsheet application

Set cell colour based on other cell colour

Postby Mpl82 » Mon Feb 18, 2019 12:28 pm

This question may have already been answered but is it possible to change the value on a cell (eg from 0 to 1 to 2 etc...) by picking up the change of colour from another cell?

Let's say I have a row or column of text or numbers (I know it sounds vague but bare with me) and when a cells text colour changes from say black to cyan, I want to have a seperate cell pick up the colour change of that cell by a value of 1 (0-1). If it's two or more cells then I would like it to pick up the colour change of those two or more cells by a total value of 2 or more (2-3-4...and so on).

Is that possible?

Any help appreciated, thanks in advance.

Marcus.
OpenOffice.org 2.0 on Windows 7 (64 bit)
Mpl82
 
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Postby RusselB » Tue Feb 19, 2019 1:32 am

Welcome to the forums.
Detecting the colour of a cell can, if I recall correctly, only be done with sophisticated macro programming.
It's a lot easier to change the colour of a cell based on the entry of, either, that cell, or another cell.
How are you setting the colour currently? Is it manually set or is it based on the entry into that cell (using Conditional Formatting)?
If you are using Conditional formatting, then you can use the same, or a similar, formula to set the cell value that you seem to be asking about.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4874
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Set cell colour based on other cell colour

Postby Mpl82 » Tue Feb 19, 2019 1:37 pm

Thanks for the reply.

Ok, that sounds a bit like assigning format by formula, that's colouring a cell depending on its value. I'd like to flip that around so I can value a cell by colour, more specifically colour change. Think of it like a "clicker" to keep track of a change of colour from black to cyan or green to red etc. I'm inputting manually by the way.

Conditional formatting is all very well but how would I (if I could) use that to have a cell automatically click up in increments of 1 for every cell that changes colour.

I can do cell count with a simple formula like: =COUNT(A1:A66) counting only the cells with numbers. Anything like that?
OpenOffice.org 2.0 on Windows 7 (64 bit)
Mpl82
 
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Postby MrProgrammer » Tue Feb 19, 2019 5:58 pm

Hi, and welcome to the forum.

Mpl82 wrote:Conditional formatting is all very well but how would I (if I could) use that to have a cell automatically click up in increments of 1 for every cell that changes colour. … Anything like that?
No. Calc's functions and formulas operate on cell values. Read section 9. Using functions and cell ranges in Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3617
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Set cell colour based on other cell colour

Postby raxell » Wed Feb 20, 2019 3:29 am

Mpl82 wrote:I'm inputting manually by the way

Do you mean inputting manually colours or numbers?
How does the cell change its colour?

And, How many colors do you need to use?
libreoffice 6.2 on windows10
raxell
 
Posts: 1
Joined: Tue Feb 19, 2019 2:36 pm

Re: Set cell colour based on other cell colour

Postby Mpl82 » Wed Feb 20, 2019 8:07 am

MrProgrammer wrote:Hi, and welcome to the forum.

Mpl82 wrote:Conditional formatting is all very well but how would I (if I could) use that to have a cell automatically click up in increments of 1 for every cell that changes colour. … Anything like that?
No. Calc's functions and formulas operate on cell values. Read section 9. Using functions and cell ranges in Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.


Ok. So changing the cell colour, be it background or text, will not initiate a change in value to another cell?

I know it can be done the opposite way because I've just done it using conditional formatting, but if there's no option for it than I appreciate your input all the same. Cheers.

Marcus.
OpenOffice.org 2.0 on Windows 7 (64 bit)
Mpl82
 
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Postby Mpl82 » Wed Feb 20, 2019 8:09 am

raxell wrote:
Mpl82 wrote:I'm inputting manually by the way

Do you mean inputting manually colours or numbers?
How does the cell change its colour?

And, How many colors do you need to use?


Hi Raxell.

Both. I'm inputting the colour changes manually but hoping to have a cell automatically keep track of the changes for each colour changing cell. Mainly green, cyan and grey. Cheers.

Marcus.
OpenOffice.org 2.0 on Windows 7 (64 bit)
Mpl82
 
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Postby robleyd » Wed Feb 20, 2019 8:12 am

So changing the cell colour, be it background or text, will not initiate a change in value to another cell?


That is another way of saying what MrProgrammer said. Functions and formulae operate on cell values, not formats.
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2516
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Set cell colour based on other cell colour

Postby Zizi64 » Wed Feb 20, 2019 8:16 am

OpenOffice.org 2.0 on Windows 7 (64 bit)

Please update your signature in this forum. I can not believe that you use such obsolete OO.o version.


You can modify the STYLE of a cell based on the VALUE of other cell.
In all of other cases need some macro programming to achieve this task.

Note: you can determine the color properties of the cells - formatted by a style or manually (directly) - by your macro, except the color properties of the conditionally formatted cells. No way to determine the actually applied style of the conditionally formatted cells.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7773
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Set cell colour based on other cell colour

Postby Mpl82 » Wed Feb 20, 2019 9:59 am

robleyd wrote:
So changing the cell colour, be it background or text, will not initiate a change in value to another cell?


That is another way of saying what MrProgrammer said. Functions and formulae operate on cell values, not formats.


Funnily enough as soon as I posted it, I read it back and just realized. Cheers.

Marcus.
OpenOffice.org 2.0 on Windows 7 (64 bit)
Mpl82
 
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Postby Mpl82 » Wed Feb 20, 2019 10:23 am

Zizi64 wrote:
OpenOffice.org 2.0 on Windows 7 (64 bit)

Please update your signature in this forum. I can not believe that you use such obsolete OO.o version.


You can modify the STYLE of a cell based on the VALUE of other cell.
In all of other cases need some macro programming to achieve this task.

Note: you can determine the color properties of the cells - formatted by a style or manually (directly) - by your macro, except the color properties of the conditionally formatted cells. No way to determine the actually applied style of the conditionally formatted cells.


Yeah I seldom use oo calc so hadn't bothered updating. It's not obsolete for what I'm doing.

Ok I'll give that a go, if I can't do it on this one I'll update and try it. Cheers.

Marcus.
OpenOffice.org 2.0 on Windows 7 (64 bit)
Mpl82
 
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Postby robleyd » Wed Feb 20, 2019 10:31 am

It is obsolete for support - there may well be differences in features that most of the helpers here may not be aware of, given the version you claim to be using was released in 2005.
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2516
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Set cell colour based on other cell colour

Postby hubert lambert » Wed Feb 20, 2019 10:40 am

Hi,

For anyone interested, there is an extension (of mine ;) ) that provides some help for this kind of problems : COUNTSTYLE.
Regards.
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
hubert lambert
 
Posts: 118
Joined: Mon Jun 13, 2016 10:50 am


Return to Calc

Who is online

Users browsing this forum: papijo and 42 guests