Highlight nearest expire date

Hi All,

I have a raw of data consists of expire date. I would like the excel to help me control the first expire first out by highlighting the nearest expire date for each materials.

Note that for each materials there are several batch with different expire date. I want the excel to highlight to me which batch i should use first base on nearest expire date. And also highlight in different color if the batch had expired.

Example is attached. I had manually highlight suggest batch to use in yellow, and expired batch in red. How should i do it with formula? Perhaps with "conditional formatting"?

Untested

In CF use
=AND(D3>=TODAY(),D3=MIN(IF($F$3:$F$81=F3,$D$3:$D$81)))
format yellow

=D3<TODAY()
format red

Assuming each "group" in section B must be treated separately, these CF formulas will work (tested and confirmed):

(1st Rule, stop if true) RED: =D3<TODAY()
(2nd Rule) =$D3=MIN(IF(($B$3:$B$81=$B3)*($D$3:$D$81>=TODAY()),$D$3:$D$81))