Conditional Formatting with multiple conditions

Eklavya 

Let start from inside. The

INT((ROW()-2)/10)+1)

returns 1 if the current row is in first 10 records, 2 for next 10, etc.

Next

INT((ROW($D$2:$D$1000)-2)/10)+1)

returns the same but as array for your entire range, i.e. array like {1,1,1,1,1,1,1,1,1,1,2,2,2,2,2...}

Comparing one with another we have array with 10 TRUE and with FALSE for the rest, depends on which row we are. Since equivalent of TRUE and FALSE are 1 and 0, next 1/(array) returns 1 for the rows from current ten and error (division on zero) for the rest.

Multiplying on range values

*$D$2:$D$1000

we have an array with column values for current ten records and others are errors.

AGGREGATE finds the first (4th parameter =1) smallest (1st parameter =15) value in the array (third parameter) ignoring all errors (2nd parameter = 6).

Next we use formula

=D2=AGGREGATE(...)

in conditional formatting rule. CF iterates our entire range to which CF rule is applied starting from first cell of the range D2. 

If for the current cell formula returns TRUE, CF applies to it defined in the rule format and do nothing in all other cases.

Rule in the formula returns TRUE if only current cell value is equal to minimum value of current ten returned by AGGREGATE.

In addition, we multiply above on (D2<>0) to exclude from formatting all blank cells in the range. Thus

=(D2=AGGREGATE(...))*(D2<>0)

 apply format if value for the current cell is equal to smallest in current ten AND cell is not blank.

Same for Max, but another range and first parameter in AGGREGATE is 14, i.e. it returns first largest value.