[Solved]Conditional Formatting - change cell background

Discuss the spreadsheet application

[Solved]Conditional Formatting - change cell background

Postby SnehalS » Wed Jul 17, 2019 8:09 am

Hello Everyone,

My name is Snehal and I have just joined this forum because i wanted some help. I have been using open office (4.1.5 with Mac 10.10.5) recently so have some problems figuring it out.
So here goes. I have an excel and have 11 columns. I want to change the background of a cell based on the values from 2 different columns.
So I have columns A and B which contains yes and no values and column C contains numeric values
So if A$1 = y and B$1 = y then the background for values in column C should become green.
And if A$2 = n and B$2 = n then the background for values in column C should become red.

I have been trying a lot but i am not getting a solution. It will be great if somebody can help. I am attaching the file as well which contains only the three columns.

Thank you. Hope to hear soon.
Attachments
ThreeCols.ods
(12.09 KiB) Downloaded 5 times
Last edited by SnehalS on Thu Jul 18, 2019 6:50 am, edited 1 time in total.
SnehalS
 
Posts: 3
Joined: Wed Jul 17, 2019 7:27 am

Re: Conditional Formatting - change cell background

Postby FJCC » Wed Jul 17, 2019 3:44 pm

In the conditional formatting I used formulas like
Code: Select all   Expand viewCollapse view
AND(B7 = "y"; C7 = "y")
Attachments
ThreeCols_fjcc.ods
(8.72 KiB) Downloaded 4 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Conditional Formatting - change cell background

Postby SnehalS » Thu Jul 18, 2019 5:32 am

Is it possible to keep the formula in A3 and it can be dragged down or copied to the other cells as well. With this I have to do the conditional formatting for every cell. As the data is small there is no issue but when the data is of more rows then that can be an issue.
Open Office 4.1.5 with MacOS 10.10.5
SnehalS
 
Posts: 3
Joined: Wed Jul 17, 2019 7:27 am

Re: Conditional Formatting - change cell background

Postby FJCC » Thu Jul 18, 2019 6:23 am

You can copy the formatting using Edit -> Paste Special and selecting to paste only the formatting. You can also set the conditional formatting for a range of cells by highlighting the cells, noticing which cell the cursor ends up in, selecting Format -> Conditional Formatting and writing the formula for the cell where the cursor is. The formatting will be adjusted appropriately for the other cells in the range.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Conditional Formatting - change cell background

Postby SnehalS » Thu Jul 18, 2019 6:26 am

Okay. Tried and its working. Thank you for the help.
Open Office 4.1.5 with MacOS 10.10.5
SnehalS
 
Posts: 3
Joined: Wed Jul 17, 2019 7:27 am

Re: Conditional Formatting - change cell background

Postby Zizi64 » Thu Jul 18, 2019 6:38 am

Yes, it is possible. But you must delete the nonworking (wrong) conditions from the attached sample files. Then the "copy" and the "drag" will work properly the conditionally formatted cellrange will be expended.

Tested in my LibreOffice 6.1.6 version. (The Conditional Formatting feature is much more advanced in the Libreoffice. you van use more than three conditions, and the manager interface is better: it can show cellranges what will be handled together.)

The fixed sample:
CFinLO.png



Your sample:
CFinLO_FJCC.png
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8133
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests