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.

Dim lastRow As Long
Dim sheetName As String

    sheetName = "Main Report"
    lastRow = Sheets(sheetName).Range("D" & Rows.Count).End(xlUp).Row

    For lRow = 6 To lastRow

        If Sheets(sheetName).Cells(lRow, "D") = Sheets(sheetName).Cells(lRow, "F") Then
            Sheets(sheetName).Cells(lRow, "D").Interior.ColorIndex = 3
        End If

    Next lRow

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:

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

Quote from KjBox

Try this

Code

Dim lastRow As Long
Dim sheetName As String

    sheetName = "Main Report"
    lastRow = Sheets(sheetName).Range("D" & Rows.Count).End(xlUp).Row
    
    For lRow = 6 To lastRow

        If Sheets(sheetName).Cells(lRow, "D") = Sheets(sheetName).Cells(lRow, "F") Then
            Sheets(sheetName).Cells(4, 6).CurrentRegion.Rows(lRow).Interior.ColorIndex = 3
        End If

    Next lRow

Display More

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.

Quote from KjBox

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.

Thanks! This worked amazingly!