Answered by:
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
Answers

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 Ahmed Morsyy Saturday, December 2, 2017 3:54 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 Ahmed Morsyy Saturday, December 2, 2017 3:54 AM
