Pretty much as simple as that -- I'm uninterested in repeats that are non-consecutive. I want to know if it's possible? See the image below (where 20 should be highlighted, but not 25).

CodePudding user response：

Answer

An alternative solution that produces slightly different results to the one player0 suggested. This solution highlights all duplicates that are next to each other, rather than only the first. Apply conditional formatting to the full row (column A is assumed in this example), then use Custom formula is and the following formula

=OR(A1=A2,IF(ROW(A1)<>1,A1=OFFSET(A1,-1,)))*NOT(ISBLANK(A1))

Explanation

Relative references are used throughout the formula, so as Google Sheets progresses down the column, each reference is updated by being moved the same number of rows down as well. In other words, the formula is always being used relative to the current cell that the conditional formatting rule is evaluating.

The IF function is used because when evaluating the top row, OFFSET cannot be used, so there is a special case for that situation. The OR checks whether the current cell is equal to either of its neighbours. OFFSET is used because A0 is not a valid cell reference. I have assumed blank cells should not be marked as duplicates, so ISBLANK is used to check for that eventuality.

If you would like to check for duplicated blank cells as well, use this instead, removing the NOT(ISBLANK(A1)) at the end.

=IF(ROW(A1)=1,A1=A2,OR(A1=A2,A1=OFFSET(A1,-1,)))

If you have a header row, you can simplify the formula using the following instead. If using this formula, only apply the conditional formatting rule to A2:A instead of A:A.

=OR(A2=A3,A2=A1)*NOT(ISBLANK(A2))

Functions used: