Post by imagrump » Wed May 18, 2016 9:15 pm
How do I change the background color of a range of cells based on a condition in one of the cells?Last edited by Hagar Delest on Thu May 19, 2016 9:02 pm, edited 1 time in total.
Reason: tagged [Solved].
Post by FJCC » Wed May 18, 2016 10:23 pm
Highlight all of the cells you want to affect. Select the menu Format -> Conditional Formatting. Set the box that reads Cell Value Is to say Formula Is. In the box to the right of that type in a formula that represents the condition for changing the cell's background color. For example
$B$2= 6It is important to use the $ to make the cell reference absolute if you want the value of one cell to control the color of several cells. Click New Style, give the new Style a name on the organizer tab and set its background color. Click OK to get out of the cell style definition dialog and set the Cell Style of the conditional formatting to be the style you just defined. That should get you what you want.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post by imagrump » Wed May 18, 2016 11:13 pm
Thank you for the quick response. You lost me after: " Set the box that reads Cell Value Is to say Formula Is." I have no idea what the formula is to change color, nor do I understand:
" Code: Select all Expand view
$B$2= 6"
Can you elaborate? The range I want to change is: A257:AJ257; the cell I am using is AJ257. If it is 334, I want the cells to become one color; if it is 660, another; if not remain as is.
Post by FJCC » Wed May 18, 2016 11:55 pm
Here is an image of how to set up the Conditional formatting dialog.I created the cell styles RedStyle and YellowStyle by clicking on the New Style button, naming them on the organizer tab and setting their background color on the Background tab.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post by imagrump » Thu May 19, 2016 12:29 am
I put the =334 into Condition 1 and everything functions fine. Putting the =660 into Condition 2 does not seem to work when I move to another row; I get "MEGA WIKIUP" in the cell when I moved to cell AJ259 and tried. AJ257 has 334 in it and AJ259 has 660. In my spreadsheet I have 20 rows which may have one of four numbers in the AJ column. I would like to change the 300, 600, and 700 numbered rows and leave the 500 unchanged. Where am I going wrong?
Open Office 4.1.1 onPost by FJCC » Thu May 19, 2016 12:45 am
It is hard to say what is wrong without looking at the file. Can you upload a file containing the 20 rows? There is an Upload Attachment tab just below the box where you type a response after clicking Post Reply.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post by imagrump » Thu May 19, 2016 1:04 am
ALL IS OK!! Somehow or another, I was screwing up. SURPRISE ! I believe that I was looking for a shortcut instead of just repeating this 20 times. It works; I can transfer it to my template and use it over and over. I had been changing the row color manually, but after 10 or 12 years I got tired of that.
THANK YOU.
Powered by phpBB® Forum Software © phpBB Limited