Formula to change cell background color

[neil-uk]

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).

[Cambirder]

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

Format > Conditional formatting.

[neil-uk]

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

[gerard24]

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 3465 times

Gérard

[neil-uk]

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

[Zizi64]

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.

[neil-uk]

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?

[gerard24]

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+