CODEWITHSUNDEEP
excelvbaduplicatesruntime-errortype-mismatch
VBAbyMBA
VBAbyMBA

Reputation: 826

Check the Duplicates in column and highlight it

I need to check the duplicates in column B I write following code which seems all right but instead I am getting Run time Error 13 type-mismatch please help. why I am getting it?

Sub duplicate()
    Dim myRange As Range
    Dim myCell As Range

    Set myRange = Columns("B:B")
    For Each myCell In myRange
        If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then ' Run-time error 13 Type mismatch
            myCell.Interior.ColorIndex = 3
        End If
    Next myCell
End Sub

Update: For further info follow https://www.youtube.com/watch?v=drZK_-zzo_4

Upvotes: 0

Views: 334

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You don't need VBA to color duplicates. Just use conditional formatting:

enter image description here

But just for your interest what caused the error in your code is, that if you loop

For Each myCell In myRange

you actually don't loop through the cells in myRange, but through the columns of myRange which is only one (column B). If you want to loop through the cells of myRange you need to change it to

For Each myCell In myRange.Cells

otherwise myCell.Value is an array of the values of column B and therefore WorksheetFunction.CountIf(myRange, myCell.Value) failed.

If you really have to use VBA, I highly recommend to create the conditional formatting with VBA instead of your code. This will be much faster, your code is extremely slow.

Option Explicit

Public Sub CreateRuleForDuplicates()
    Dim MyRange As Range
    Set MyRange = Columns("B:B")
    With MyRange
        .FormatConditions.AddUniqueValues
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).DupeUnique = xlDuplicate
        .FormatConditions(1).Interior.ColorIndex = 3
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

Note that if you run the code multiple times it will add a new rule every time. So you might want to delete all old rules in that range .FormatConditions.Delete before adding a new one .AddUniqueValues.

Upvotes: 3

Related Questions