+ Reply to Thread
Results 1 to 6 of 6

Highlight dublicates in one column with macro

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Highlight dublicates in one column with macro

    HI. I am trying to make a macro to highlight the dublicates in a certain column, but cant really make it work.

    I need a macro because its a part of a bigger project with many macroes.

    I would like to highlight all dublicates for the names or numbers in column C Sheets("Final Match") with a macro.
    I have found one but it marked all the blanks also.
    So i need a macro, which can highlight all the dublicates in the used range (jump over blanks). In the upper sheet and column.



    Thanks in advance

    Sincerely

    Abjac

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Highlight dublicates in one column with macro

    So, add to your routine a check on the cell and make sure it is not an empty cell before doing anything to it.

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Highlight dublicates in one column with macro

    If you want to highlight duplicates in Col A then you can use conditional formatting.

    Select Cel A2 and apply a new rule for conditional formatting.
    =VLOOKUP(A2,$A$1:A1,1,0)=A2
    Apply you required format (say fill yellow).

    Apply another formatting rule to this cell.
    =A2=""
    Apply a format so that it looks the same as it normally would (this then 'hides' blank duplicates).

    With A2 selected choose format painter then select all cells below you want to check for duplicates.
    Say thanks, click *

  4. #4
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Highlight dublicates in one column with macro

    Thanks to both of you. I cant use a formula because its part of a bigger project, where there run other macroes.
    JBeaucaire. I have tried to use your solution but cant make it work with my code.

    My code works fine, but continue highlight the cells, up to 200. Even if they are blank.

    Please have a look at my code and also the test sheet i have uploaded.

    Thanks

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Highlight dublicates in one column with macro

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Highlight dublicates in one column with macro

    HI AB33. Perfect. It works like a charm. Do the job exactly as it have to do. So thanks alot really great.
    Muchos gracias

    Abjac

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1