Posts by gprieto

Thanks! This worked amazingly!

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.

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.

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'"))

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