Check for matches and apply conditional format how to do?

vivekisvivek

New Member
Joined
Sep 20, 2014
Messages
3
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top