# Highlight adjacent cells only in column • ### Question

• Hello,

I'm trying to highlight all duplicate cells in a column, but they should be adjacent cells only.

I used the conditional formatting to do this, but I got the only first occurrence highlighted. I do not know how to modify the formula to have all adjacent duplicated cells highlighted together. The result should be as below Appropriate any help.

Thanks

Friday, December 1, 2017 11:25 AM

• If you were starting on row 2 for the conditional formatting then you could use a very simple formula by selecting "Use a formula to determine which cells to format". However, that means you need to have the Applies to range commence on row 2 and the formula as follows where \$A2 is the first cell of the conditional formatting "Applies to" range.

=OR(\$A2=\$A1,\$A2=\$A3)

However, if you want to apply the conditional formatting from row 1 then the formula has to manage the error of attempting to compare the first row to the row above row 1 (and that row does not exist and returns an error). The formula will need to be as follows if the "Applies to" range commences on row 1.

=IFERROR(OR(\$A1=OFFSET(\$A1,-1,0,1,1),\$A1=\$A2),\$A1=\$A2)

Regards, OssieMac

• Marked as answer by Saturday, December 2, 2017 3:54 AM
Saturday, December 2, 2017 1:56 AM

### All replies

• If you were starting on row 2 for the conditional formatting then you could use a very simple formula by selecting "Use a formula to determine which cells to format". However, that means you need to have the Applies to range commence on row 2 and the formula as follows where \$A2 is the first cell of the conditional formatting "Applies to" range.

=OR(\$A2=\$A1,\$A2=\$A3)

However, if you want to apply the conditional formatting from row 1 then the formula has to manage the error of attempting to compare the first row to the row above row 1 (and that row does not exist and returns an error). The formula will need to be as follows if the "Applies to" range commences on row 1.

=IFERROR(OR(\$A1=OFFSET(\$A1,-1,0,1,1),\$A1=\$A2),\$A1=\$A2)

Regards, OssieMac

• Marked as answer by Saturday, December 2, 2017 3:54 AM
Saturday, December 2, 2017 1:56 AM
• Thank you OssieMac, that was brilliant!
Saturday, December 2, 2017 3:54 AM