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

[st_judeu@yahoo.com]

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.

[Azien Heart]

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

[sktneer]

Try making a New Rule for conditional formatting for the range starting from B4:BX152

=ROW()=MAX(INDEX(((B$4:B$152)<>"")*(ROW(B$4:B$152)-ROW(B$4)+1),0))+3

This will highlight the last non empty cell in the column (even if it contains a zero).

And if you want to exclude Zero's, try this

=ROW()=MAX(INDEX(((B$4:B$152)<>"")*((B$4:B$152)<>0)*(ROW(B$4:B$152)-ROW(B$4)+1),0))+3

This formula will highlight the last cell in a column where it has a value greater than 0.

[st_judeu@yahoo.com]

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.

[st_judeu@yahoo.com]

Please U still need help to resolve this.

[sktneer]

To give you an idea, I have applied conditional formatting in the range AZ4:BE41. Notice a lookup table in col. CA:CB. If this conditional formatting suits your requirements, you will have to create few more lookup table in the same fashion and make the changes in the current formula to reference different lookup tables for the formatting of different columns. (Because in the sheet you have merged cells which are barrier in making only one lookup table which can be referenced to all the columns. I have used the following formula for the conditional formatting in the range AZ4:BE41

=ROW()=MAX(INDEX((INDEX($AZ$4:$BE$41,,MATCH(AZ$2,$AZ$2:$BE$2,0))<>"")*(INDEX($AZ$4:$BE$41,,MATCH(AZ$2,$AZ$2:$BE$2,0))<=INDEX($CB$4:$CB$9,MATCH(AZ$2,$CA$4:$CA$9,0)))*(ROW($AZ$4:$AZ$41)),0))

Does this help?