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 ,

in comparison I've used -ceq (exact match) - please change to fit your requirements
A -ceq a = $false
A -eq a = $true

For ($i = 2; $i -le $rows; $i += 1)  
{  
$value = $w1.Cells.Item($i,2).Text  
      
    For ($j = $i + 1; $j -le $rows; $j += 1)  
    {  
        $compareValue = $w1.Cells.Item($j, 2).Text  
  
        if ($value -ceq $compareValue)  
        {  
            if ($array.Count -gt 0)  
            {  
                if(!$array.Contains($i))  
                {  
                    $array += $i   
                }  
                if(!$array.Contains($j))  
                {  
                    $array += $j  
                }  
  
            }  
            else {  
                $array += $i   
                $array += $j  
            }  
  
        }  
  
    }  
  
}