Highlighting duplicate cells adjacent to one another across multiple rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2641514%22%20slang%3D%22en-US%22%3EHighlighting%20duplicate%20cells%20adjacent%20to%20one%20another%20across%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641514%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20trying%20to%20highlight%20cells%20that%20share%20the%20same%20value%20and%20that%20are%20adjacent%20to%20one%20another%2C%20but%20copy%20this%20formula%20down%20to%20over%20900%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20selecting%20all%20the%20cells%20and%20then%20clicking%20Conditional%20Formatting%2C%20New%20Rule%2C%20Use%20a%20formula%20to%20determine%20which%20cells%20to%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20in%20the%20%E2%80%98%3CU%3EE%3C%2FU%3Edit%20the%20Rule%20Description%3A%E2%80%99%20I%20have%20tried%20%3CSTRONG%3E%3D%24A%241%3D%24B%241%20%3C%2FSTRONG%3Eand%20then%20have%20selected%20it%20to%20format%20the%20cell%20by%20highlighting%20an%20adjacent%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20this%20only%20completes%20the%20formula%20for%20the%20first%202%20cells%20(Row%201)%20in%20this%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20as%20a%20result%2C%20I%20would%20like%20cells%20A1%20and%20B2%20to%20be%20highlighted%2C%20and%20cells%20A5%3AA10%20and%20B5%3AB10%20also%2C%20and%20then%20the%20cells%20in%20between%20to%20be%20left%20blank%20so%20I%20know%20what%20cells%20are%20not%20sharing%20the%20same%20value%20as%20their%20adjacent%20neighbour%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20using%20a%20PC%20and%20Windows%2010%2C%20with%20Office%20365.%20Microsoft%20Home%20and%20Business%202016.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99ve%20got%20over%20900%20lines%20to%20do%20on%204%20spreadsheets%20and%20haven%E2%80%99t%20got%20the%20patience%20to%20go%20through%20each%20set%20of%20results%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2641514%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2641621%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20duplicate%20cells%20adjacent%20to%20one%20another%20across%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127007%22%20target%3D%22_blank%22%3E%40Shippady%3C%2FA%3E%26nbsp%3BUse%20%3D%24A1%3D%24B1%20in%20stead.%20Then%20it%20shall%20work%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi Folks,

 

I’m trying to highlight cells that share the same value and that are adjacent to one another, but copy this formula down to over 900 rows.

 

I have tried selecting all the cells and then clicking Conditional Formatting, New Rule, Use a formula to determine which cells to format.

 

Then in the ‘Edit the Rule Description:’ I have tried =$A$1=$B$1 and then have selected it to format the cell by highlighting an adjacent match.

 

However this only completes the formula for the first 2 cells (Row 1) in this case.

 

For example as a result, I would like cells A1 and B2 to be highlighted, and cells A5:A10 and B5:B10 also, and then the cells in between to be left blank so I know what cells are not sharing the same value as their adjacent neighbour cell.

 

I’m using a PC and Windows 10, with Office 365. Microsoft Home and Business 2016.

 

I’ve got over 900 lines to do on 4 spreadsheets and haven’t got the patience to go through each set of results manually.

 

Please help! Thanks in advance!

1 Reply

@Shippady Use =$A1=$B1 in stead. Then it shall work as desired. Note that empty cells are equal to zero. So A11: B12 will also be marked. If you don't want that, add another rule (should be the first one like =OR(ISBLANK($A1),ISBLANK($B1)) and check the box "Stop If True"