Explanation
This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10).
The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function:
COUNTIF(range1,B5) // count in range1
COUNTIF(range2,B5) // count in range2
COUNTIF will either return zero (evaluated as FALSE) or a positive number (evaluated as TRUE) for each value in both ranges.
If both counts are positive (i.e. non-zero), the AND function will return TRUE and trigger the conditional format.
Related formulas
Highlight duplicate values
Highlight duplicate rows
Range contains duplicates
Related functions
COUNTIF Function
The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...
AND Function
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF...