VBA - Highlight Entire Row - Based on Duplicate

  • Hi Everyone,


    Currently, I am fairly new to creating Excel Macros. I have been using the Macro Recorder and editing the macros to be a bit more versatile for my duties at work.

    I am trying to automate a few things and as well trying to add some things to a report to make it easier for my backup if I am ever out sick or on vacation.


    What I am trying to do is have conditional formatting set through a macro that will Highlight the entire row if the value in Col D (Rows 6 - final row with data) match / duplicate the value in Col F (F6 - final row with data) *Ignoring the first occurrence if there is more than 1*


    Currently, I have the below code that I got from stackoverflow though it only highlights the Cell that is a duplicate, but I can't seem to figure out how to convert it to highlight the full row:


    Any help with this will be great. I know I can use conditional formatting within excel but automating this into a macro would be easier than teaching my backup how to do this.

  • Go to Best Answer
  • Try this

    You may need to use .Rows(lRow + 1) as you do not say if you have a header row, or what is in rows 1 to 5

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi there sorry about that, it was a bit late when I wrote this so I can explain a bit more.


    All of this is happening in a Pivot table sheet (Col A5 -> E5) Rows 1 thru 4 are essentially used for Data and a Macro Button that will Refresh the Pivot Table data when we overwrite the files in the shared folder. Columns F thru H (Rows 6 and down) is bringing in data from a second Pivot Table using an IF / Vlookup formula.


    I would like to essentially want if a duplicate PO # is found in both "PO#" [Col D] and "Open Contract PO" [Col F] that it will fill in the entire row in red (ex. A6 thru H6) making it easier for someone to see if a new PO is needed and for what Invoice / Open Contract period.



    Sample of the formulas being run in Col's F thru H below:

    Code
    =IF(A564="","",IFERROR(VLOOKUP(A564,'Continuation Action'!A:B,2,0),"Not in 'On Rent Report'"))
  • Try this

    You may need to use .Rows(lRow + 1) as you do not say if you have a header row, or what is in rows 1 to 5

    Hi Kj,


    Thank you again for your quick reply earlier. I tried the code you provided to try, when I enter it with how you originally had it and then edited it with 1Row + 1 below is what tends to happen.


    As you can see in the screenshot, it looks like it's picking up some duplicates but it's definitely missing the ones I have highlighted. I don't know what might cause it to do this, the original code I know caught each of the duplicates but just didn't highlight the entire rows.


  • Can you attach your workbook not just ban image

    Sure thing sorry about that I had to edit the workbooks a little to remove any company data that wasn't relative to the pivot tables.

    I am including the other two files that get refreshed into the main report and will probably break the Refresh macro where the duplicate coding is in.

    • Best Answer

    Try this instead. You can add a call to this macro at the end of your Refresh code so that highlighting of duplicates is updated after a refresh.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 2 times, last by KjBox ().

  • gprieto

    Selected a post as the best answer.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!