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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,218,620
Messages
6,143,520
Members
450,492
Latest member
Rusbus1972

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
Back
Top