Conditional Formatting with formula wont apply styles across multiple rows

Question

I read loads of answers here, but none of them applies to my issue I think.

I need to have a coloured column based on a dropdown list value. If it's Not Paid, the A column has to be red if it's Paid, it has to be green.

When I apply =K12="Not Paid" formula, styles apply only for only one cell. if I shoose apply to all empty cells it works as expected. but as soon as I use any formula styles are all over the place.

Any ideas?

Here is an example of the spreadsheet I am working with https://docs.google.com/spreadsheets/d/18Y-2jeKue9aH4gGdKHZjECygo47JFOp2W11C2sy7fJk/edit#gid=553917738

Answer 1

This is due to the conditional formatting via formula that can be a little tricky. The solution you need is:

=K$12="Not Paid"

This is because when you use =K12="Not Paid" it uses relative references. That means that if you apply this conditional formula in a range like "A12:A2", for cell "A12" the formula will be exactly as you wrote, but for cell "A13" it will have a formula like =K13="Not Paid", which is not true. I hope you can take a look on this more in depth tutorial.