Umut

Here is the model, and once you have it the dollar signs stop being guesswork.

The formula is written from the point of view of the top-left cell of the range you selected, and then copied to every other cell in that range — exactly as if you had typed it in that first cell and dragged it across and down. Each cell evaluates its own copy, and colours itself based on its own result.

So two things follow, and they are the whole answer:

Your range has to be the whole row block, not just the status column. If you only selected the status column, only those cells can ever colour, because those are the only cells that got a copy of the formula. This is your actual bug.

The column reference has to be locked and the row must not be. If your data starts at A2 and status is in column E, select A2 to H500 and use a formula like =$E2="overdue".

• $E locks the column, so every cell across the row still looks at column E rather than drifting to F, G, H as the formula is copied sideways. This is what makes the whole row respond to one cell.
• 2 with no dollar lets the row move, so row 7's copy checks E7. This is what makes each row independent.

All four of your wrong results are one of those two being backwards, and now you can predict which.