Ask Your Question
0

Setting Cell Style either with formula or conditional Formatting! [closed]

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-03 00:17:41.439902

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

Question Tools

1 follower

Stats

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

Seen: 3,701 times

Last updated: Apr 05 '16