Check for matches and apply conditional format how to do?

[vivekisvivek]

In column A I have values in this format ( Combination ) AB-BY-GH-TY SF-DF-DH-DH DG-DG-HY-YJ DH-TD-FG-DF . . . . and so on. There are around 10000 rows like this Im in need of a VBA Macro that can do this : IT should move down cell by cell from 50th Row to last row doing this : >Start >Match present cell value with above rows ( values ) >If duplicate , format cell ( red ) >If unique ( blue ), format cell ( blue ) > Next Cell ( Move 1 cell down ) It starts with 50th row , checks whether the 50th row cell value matches with any of cells value from row 1 to row 49. If matches ( exact match ), format will fill red. If not found(unique), format with Blue. Then move to 51st row cell. I cant take this data in two tables. Im getting confused how this could be done with single same column. Like each time taking range above it excluding the present cell as it moves down. Hope I find some help. Thanks in advance.

 

[mikerickson]

VBA is not needed.

Step 0) format all the cells from 50 downward blue

1)Select A50
2) Put Conditional Formatting on that cell with the formula =ISNUMBER(MATCH(A50, A$1:A49, 0)) to make the format red
3) copy that CF to the rest of the cells in column A.

 

[Peter_SSs]

A couple of comments on Mike's suggestion..

1. For the red this should suffice since if is isn't a number, the CF will not be applied.
=MATCH(A50, A$1:A49, 0)

2. If there might be blanks in the data from row 50 down, then manually formatting blue may possibly not be desirable?
If blanks are to be left unformatted then for the blue:
=AND(A50<>"",ISNA(MATCH(A50,A$1:A49,0)))