Set cell colour based on other cell colour

Discuss the spreadsheet application
Post Reply
Mpl82
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Set cell colour based on other cell colour

Post by Mpl82 »

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)
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Set cell colour based on other cell colour

Post by RusselB »

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.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
Mpl82
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Post by Mpl82 »

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)
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Set cell colour based on other cell colour

Post by MrProgrammer »

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.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
raxell
Posts: 1
Joined: Tue Feb 19, 2019 2:36 pm

Re: Set cell colour based on other cell colour

Post by raxell »

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
Mpl82
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Post by Mpl82 »

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

Post by Mpl82 »

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)
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Set cell colour based on other cell colour

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Set cell colour based on other cell colour

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Mpl82
Posts: 6
Joined: Mon Feb 18, 2019 12:16 pm

Re: Set cell colour based on other cell colour

Post by Mpl82 »

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

Post by Mpl82 »

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)
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Set cell colour based on other cell colour

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Set cell colour based on other cell colour

Post by hubert lambert »

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
Post Reply