Advance Conditional Formatting

[nickh1981]

Hi All,

I have an excel file with conditional formatting which i get it done with this forum help. I would like it to be in more advance mode now. So i am not sure should i open new thread or use back the previous thread for this request. Please correct me if i am wrong.

Anyway, I will explain what i need. In the excel file i have material expire date. The rule applys to the date is first expire first out. Refer to the excel file "Column D". It will highlight "green" for the next date going to expire.

Now my question is if i have stock out the highlighted materials, i want it to highlight next nearest expire date. Example "row 7 & 8" i have stocked out the materials. Column D should change the color to "pink" for the materials that already stocked out and highlight the next expire date in green, in this case will be "row 10 - 13"

How should i do this?

[JBeaucaire]

1) Delete the existing rules
2) Add these rules so that when done they appear IN THIS ORDER, be sure to click STOP IF TRUE on the first two:

PINK: =K3>=H3 (stop if true)
RED: =D3<TODAY() (stop if true)
GREEN: =$D3=MIN(IF(($B3=$B$3:$B$81)*($D$3:$D$81>=TODAY())*($H$3:$H$81>$K$3:$K$81),$D$3:$D$81))

http://screencast.com/t/nfRoymtX

[nickh1981]

Hi JB,

Thanks for the fast response. It works exactly the way i want.