Formula to change background colour if Y or N is in cell

Hi all,

I am new to Libre, its what i have installed on my work laptop.

I am trying to create a spreadsheet that will automatically change the background colour of a cell if i enter a Y or N, so for the Y i want it to be green and for the N i want it to be red.

I have tried following tutorials and nothing is working, the cells dont change, so clearly doing something wrong.

Can someone give me an idiots step by step please lol

I have tried following tutorials

This doesn’t tell any potential reader of your question, what you have tried exactly. Please assure to explain what you did, which tutorial you are referring to and where it failed (a sample file of the result of your efforts is often the key to an answer)


the buzzword here is “Conditional Formatting”.

Step1 - Preparation of required styles for Conditional Formatting

Conditional Formatting requires a style to be applied based on one (or more) condition (See Step 2). Hence we need to create cell styles for a green background and one for a red background. This is what this step is about:

  • Go to menu Styles -> Manage Styles (or use F11)
  • Click the icon Cell Styles (leftmost beneath the word Styles)
  • Right click on Default and select New and then tab Organizer
  • Provide a name (e.g. BGgreen for a green background - but it is up to you)
  • Change to tab Background, click button color and select your desired green color
  • Click OK

Repeat the same thing for red colored background (e.g. BGred)

Step2 - Define the Conditional Formatting
(This example assumes that Y or N should be input in column A, starting in row 2 (row 1 should be a header line with no data input)

  • Select cell A2
  • Go to menu Format -> Conditional -> Condition
    Condition 1 Cell value is equal to and type "Y" into field and Apply Style select BGgreen
    Condition 2 Cell value is equal to and type "N" into field and Apply Style select BGred
    Cell Range Enter A2:A1048576

See the following sample file: ConditionalFormatting-Yes-No.ods

Hope that helps.

Thank you :slight_smile: That worked perfectly, i think the error i was making was not putting “” either side of the letter as this was how i was trying before, just with Y or N

Yes - and my versions tells this:
image description