Conditional formatting to apply only on the last non blank cell in a column

Good day all,

The attached file is my stock control register. In the balance section (the last section), I have inserted conditional formatting that highlights items that have fallen below reorder level. This is to enable me know when to ask for replenishment for each stock. In my formatting, the system keeps highlighting until new order is made. But what I actually want is for the highlight to apply only on the last non blank cell for any item that has fallen below reorder level. This means that whenever new entries are made, the highlight shifts to the new last non blank cell so that at every point in time, I will not have more than one highlighted cell in a column.

Hope my explanation is clear enough.


Thanks.

Try having in your conditional formatting formula =And( , <>0)
Example your AZ column: =AND($AZ4<=150,$AZ4<>0)

Thanks for your contributions. But my challenges have not been addressed. Azien Heart's contribution like my attached file, highlights all the the cells that fall below reorder level. The only difference is the exclusion of the cells with "zero" value.

Sktneer's contribution highlights the last cell in the column.

What I actually want is a formular that will highlight the last cell that is less than or equal to the reorder level. This means that if there is replenishment in the succeeding cell, the highlight will disappear. In that way, there will not be more than one highlighted cell in a column.

Below are the reorder levels for all the products.

Reorder Levels

Freshyo 1 lit (Plain) 150
Freshyo 1 lit (S/B) 100
Freshyo 500ml (S/B) 100
Freshyo 500ml (Plain) 500
Freshyo 150ml (Plain) 500
Freshyo 150ml (Strawberry) 200
Nature Fresh 1 Lit (Orange) 50
Nature Fresh 1 Lit (Pineapple Coconut) 50

Thanks once more for your responses as I also hope my last explanation will give you a clearer picture of what I want.


Thank you.

Please U still need help to resolve this.