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.

[erAck]

Please do not recommend the use of the STYLE() function. It sets the cell style during formula evaluation, whenever a recalc is triggered for that formula, whereas conditional formatting is evaluated for the current view only, not replacing an applied cell style. STYLE() is also not interoperable with other spreadsheet implementations. The only real use of STYLE() is to temporarily highlight a cell with a timeout along with the call of an asynchronous Add-In function when that delivers its result.

See also STYLE function …