locat and highlight duplicates

[Nico Learning]

Hi

Hope someone out there can help me with this one,
im trying to identify the duplicate that i have on my spread sheet, i found a really good VBA code that work really will but the problem that i have is that i dont know how to amend it to show across several column (basically i want to to show me if there is any duplication in column A,B,C and D.) i.e. if i have john in B1 and also have John in D3 i want it be able to locate these by highlighting them. please see to code below, "found it on the internet"

Sub MakeDupsYellow()

Dim myRange As Range
Dim strColToSort As String

'Note: first cell in column is considered a heading
' and is not included in the filter.

strColToSort = "B"

Application.ScreenUpdating = False

Set myRange = Columns(strColToSort & ":" & strColToSort)
myRange.Interior.ColorIndex = 45
myRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
myRange.Interior.ColorIndex = xlNone
ActiveSheet.ShowAllData

Application.ScreenUpdating = True

End Sub


Kind Regards
Nick

 

[jbeaucaire]

I would use CONDITIONAL FORMATTING for this, not a macro.

For instance, highlight A1:D100
Click Format > Conditional Formatting
Condition1: Formula Is: =COUNTIF($A$1:$D$100,A1)>1
Format...Pattern...yellow

 

[Nico Learning]

Thank you very much, works perfectly

 

[jbeaucaire]

Glad to hear it.

 

[lox1985]

can someone help me expand on this code? I finds the values that are duplicates, no problem there. But I need to delete them and I can't figure why my if statement doesn't work. I have to have this code because I have many loan #s and they update daily

 

[Legacy 14611]

Try this. You can remove the apostrophes to suit your deletions etc. Or replace ActiveSheet.UsedRange by Selection if you want to choose your own range of relevance rather than the whole sheet.

Sub dupes()
Dim z As Object, e As Range
With ActiveSheet.UsedRange
Set z = CreateObject("Scripting.Dictionary")
For Each e In .Cells
    If Not IsEmpty(e) Then
        If Not z.exists(e.Value) Then
            z.Add e.Value, Empty
        Else
            e.Interior.Color = vbYellow
            'e.ClearContents
        End If
    End If
Next
'.Cells.SpecialCells(4).Delete xlUp
End With
End Sub

 

[Nico Learning]

Thank you very much for you help
however ive gone with the conditional formatting option with the code below

=AND(A1494<>"",COUNTIF($A$2:$E$1500,A1493)>1)

regards
Nick