Ask Your Question
0

Setting Cell Style either with formula or conditional Formatting!

asked 2016-04-04 08:42:35 +0200

farrinux gravatar image

updated 2016-04-04 12:36:52 +0200

Hello All,
I have the following formula in a cell

=IF(D8<60,"OK","Change")

It works just fine. Depending on cell D8 the cell will say OK or Change. If the Cell shows "OK" I would like the cell background to be Green. If the cell shows "Change" I would like the cell background to be Red.

I have tied doing this with in the formula and I can not get it to work. I have also tried it using conditional formatting and I can not get that to work either.

I do have two Styles setup one "Red" and one "Green" where the cell background is the respective color. I just can not figure it out. Any help would be grateful.

edit retag flag offensive close merge delete

Comments

Do you mean something like =IF(D8<60;"OK"&IF(STYLE("Green");"";"");"Change"&IF(STYLE("Red");"";""))?

JohnSUN gravatar imageJohnSUN ( 2016-04-04 10:28:10 +0200 )edit

Thanks for the reply. Will give it a try.

farrinux gravatar imagefarrinux ( 2016-04-04 12:10:52 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2016-04-04 10:51:05 +0200

pierre-yves samyn gravatar image

updated 2016-04-04 10:54:49 +0200

Hi

You can use, for example: =IF(D8<60;"OK";"Change")&T(STYLE(IF(D8<60;"Green";"red")))

Explanation: STYLE returns the value 0 so we use T : returns a blank text string if the target is 0.

The ConditionStyle.ods attached also shows the example of conditional formatting

note: attention to IF(D8<60;"OK";"Change"): returns OK if D8 is empty...

Regards

edit flag offensive delete link more

Comments

D8 will always be a number. Thanks I will give this a try.

farrinux gravatar imagefarrinux ( 2016-04-04 12:27:33 +0200 )edit

Thank you very much. Works like a charm. I read the help section on adding the formatting in the formula. However it would not even work. While your answer is similar it works and I tried some thing very close to that, just didn't know about "T" and of course some of what needed brackets. Thanks again.

farrinux gravatar imagefarrinux ( 2016-04-04 12:32:41 +0200 )edit
1

answered 2016-04-05 14:20:58 +0200

LibreGuy gravatar image

I read your question and I don't think the given answer is the best way to achieve what you want. There is a far more easy solution to solve your problem.

  • Open your spreadsheet and place the cursor on the cell that requires conditional formatting.
  • Go to the menu: Format > Conditional Formatting > Manage
  • Click button 'Add'
  • It already says "Cell value is equal to". In the field on the right you type "OK" (including the quotes)
  • On the next line it says "Apply Style". Use the drop-down menu to select your green style
  • Click the 'Add' button again.
  • Again it says "Cell value is equal to". In the field on the right you type "CHANGE" (including the quotes)
  • On the next line use the drop-down menu again, this time to select your red style
  • Click the 'OK' button and you're done

THIS is the correct way to set-up conditional formatting. You can apply one "rule" not only to 1 specific cell, but to a whole range of cells simply by adjusting the cell range.

Hope this will help you. It's a far more efficent way to implement conditional formatting.

edit flag offensive delete link more

Comments

Hi @LibreGuy

I wonder if you read carefully as my answer that question because my example uses conditional formatting and answers the question on the use of the function

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-04-06 11:15:09 +0200 )edit

Before I posted I had tried using the Conditional Format Manager. Seems as though it does not like text as a value. I could not get it to work. I also in my search found that one could also include it in his formula. I tried that and could not get that to work. Pierre was the first to post and his formula worked. as to being a not "correct way" to do it. There is always more than one way to skin a cat.

farrinux gravatar imagefarrinux ( 2016-04-08 15:30:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-04-04 08:42:35 +0200

Seen: 2,264 times

Last updated: Apr 05 '16