Setting Cell Style either with formula or conditional Formatting!

[farrinux]

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.

[JohnSUN]

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

[farrinux]

Thanks for the reply. Will give it a try.

[LibreGuy]

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.

[PYS]

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

[farrinux]

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.

[PYS]

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

[farrinux]

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

[farrinux]

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.