Formula to change cell background color

Discuss the spreadsheet application

Formula to change cell background color

Postby neil-uk » Sun Nov 14, 2010 12:18 am

Hi guys,

Is it possible in Calc to create a formula etc to change the background colour of a Box?

So, if 'Blue' was written in A1, A1's background colour would change to Blue.

Thanks
Neil

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
Last edited by neil-uk on Sun Nov 14, 2010 12:42 am, edited 1 time in total.
NeoOffice 2.2.3 with MacOS 10.4
neil-uk
 
Posts: 11
Joined: Sun Nov 07, 2010 1:01 am

Re: A would it work question

Postby Cambirder » Sun Nov 14, 2010 12:34 am

Yes, provided you don't need to set more than 3 conditions you can do it through conditional formating,

Format > Conditional formatting.
OOo 3.3 on Windows 7 & 3.2.1 on Mint 10
User avatar
Cambirder
Volunteer
 
Posts: 647
Joined: Thu Nov 22, 2007 1:01 am

Re: A would it work question

Postby neil-uk » Sun Nov 14, 2010 3:20 am

Hi, Thanks for this.

I was hoping for a few more colours - such as Red, Blue, Green, Yellow, Orange, Purple, White, Brown and Grey.

Are there any other options?

Thanks
Neil
NeoOffice 2.2.3 with MacOS 10.4
neil-uk
 
Posts: 11
Joined: Sun Nov 07, 2010 1:01 am

Re: Formula to change cell background color

Postby gerard24 » Sun Nov 14, 2010 11:41 am

Hi,

If you have more than 3 colors, you can use the STYLE() function.

First, define style you need (Blue, Red, Green.....) with stylist (F11)
Second, use STYLE() in conditional formatting.

Example :
STYLE.ods
(8.31 KiB) Downloaded 820 times

Gérard
LibreOffice 4.1.3 on Windows Vista
gerard24
Volunteer
 
Posts: 537
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Formula to change cell background color

Postby neil-uk » Sun Nov 14, 2010 9:05 pm

Hi Gerard,

Thanks for sending this example over.

But, as I changed the drop down boxes for different colours, the backgrounds didn't seem to change.

I can't seem to work out the coding, and the Help menu isn't much help.

Can you explain it in some more detail please?

Thanks
N
NeoOffice 2.2.3 with MacOS 10.4
neil-uk
 
Posts: 11
Joined: Sun Nov 07, 2010 1:01 am

Re: Formula to change cell background color

Postby Zizi64 » Sun Nov 14, 2010 10:24 pm

Cellstyles.png


You need to create different cellstyles with different background colors based on Default cell style.
You must to name those cellstyles as color names, When you select (or type in) a color name into a cell, the applied "Conditional format" option will "compute" the style name from the cell content, and will apply it on cell.
Tibor Kovacs, Hungary; AOO4.0.1, LO3.6.4, LO4.2.2 on WinXPprof.SP3
Zizi64
Volunteer
 
Posts: 1784
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formula to change cell background color

Postby neil-uk » Sun Nov 14, 2010 11:12 pm

Hi Guys,

Thaks for this.

The only issue I have now is that I want to apply these to multiple cells around the Spreadsheet.

If I select them all is going to muck up whats in my other formulas.

Will doing STYLE() automatically make each cell I have edited see itself, or will I need to go through each one and so STYLE(A1), STYLE(B5) etc?
NeoOffice 2.2.3 with MacOS 10.4
neil-uk
 
Posts: 11
Joined: Sun Nov 07, 2010 1:01 am

Re: Formula to change cell background color

Postby gerard24 » Sun Nov 14, 2010 11:45 pm

You can select all the cells you want to apply conditional formatting and write the formula STYLE(Cell wich have focus).
Or format one cell and copy to others with the paintbrush.
Or copy/paste special > Formats

A+
LibreOffice 4.1.3 on Windows Vista
gerard24
Volunteer
 
Posts: 537
Joined: Sat Oct 30, 2010 5:12 pm
Location: France


Return to Calc

Who is online

Users browsing this forum: No registered users and 9 guests