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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,220
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
55,026
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)))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,025
Messages
5,856,898
Members
431,837
Latest member
megantang

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