Macro To Highlight Duplicate Records

Excel VBA Highlight Duplicate Values           


         The duplicate records are searched in the used ranges of all worksheets by Excel WorksheetFunction.CountIf  method  as following :
Sub duplicates_coloring()
Dim mycell As Range, syf As Byte

For syf = 1 To Sheets.Count
Sheets(syf).Select
ActiveSheet.UsedRange.Select

For Each mycell In Selection
    If WorksheetFunction.CountIf(Selection, mycell.Value) > 1 Then
        mycell.Interior.ColorIndex = 8
        mycell.Borders.Weight = xlThin
        mycell.Borders.ColorIndex = 44
    End If
Next
Next
Sheets(1).Select
End Sub

Background of found records are painted and new borders are drew  :
For Each mycell In Selection
If WorksheetFunction.CountIf(Selection, mycell.Value) > 1 Then
mycell.Interior.ColorIndex = 8
mycell.Borders.Weight = xlThin
mycell.Borders.ColorIndex = 44
End If
Next




If desired , the processes can be recovered with "Undo Button".


No comments:

Post a Comment