none
Highlight adjacent cells only in column RRS feed

  • 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

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
    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 Ahmed Morsyy 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