Find duplicates in excel

Question

I have many excel files as below, where I want to find the duplicate values in a column and highlight the entire row with green color. I have the below code, which is only highlighting the duplicate cells in green color. How can I highlight the entire row which has duplicate values in column B.

![$ex = New-Object -ComObject Excel.application  
$ex.visible = $false  
$wb = $ex.Workbooks.Open("C:\Temp\Book2.xlsx")  
$w1= $wb.Worksheets.item(1)  
$w1.activate()  
$r= $w1.Range("B2").EntireColumn  
$dup = $r.FormatConditions.AddUniqueValues()  
$dup.DupeUnique = 1  
$dup.Font.ColorIndex =4  
$wb.Save()  
$wb.Close()][1]  

Answer 1

Hello @Sam White

I know it may sound very intuitive, but have you tried with .EntireRow property?

https://learn.microsoft.com/en-us/office/vba/api/excel.range.entirerow

--If the the reply is helpful, please Upvote and Accept as answer--

Answer 2

There is no EntireRow property for $dup, the only properties of $dup are:

PS C:\WINDOWS\system32> $dup

Application :
Creator :
Parent :
Priority :
StopIfTrue :
AppliesTo :
DupeUnique :
Interior :
Borders :
Font :
Type :
NumberFormat :
PTCondition :
ScopeType :

Answer 3

Any suggestions on this?

Answer 4

Hello,

@Sam White - I've achieved this like that:

• I've created two for loops to go thru the column with duplicated values, compare them (n and n+1) and save row number to array
• At the end I've used foreach loop to go for all values in array and change the font color:
• For entire row - $w1.Rows.Item($a).font.colorindex = 3 #red
• For cell only - $w1.Cells.Item($a,2).font.colorindex = 3 #red

Thank you.

Answer 5

@Anthony Axlen , thanks. Can you please provide the details of the for loop you created.