find duplicate values in a column based on user input

152 views Asked by At

I'm trying to find the duplicate cells in a column. I changed the code from manually giving the range to user defined. But it gives me error. What should be modified from the below code to take user input and process.

Sub DupEntry()
Dim cel As Variant
Dim rng As Range
Dim clr As Long
Dim colname As Variant
Dim j As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

colname = Application.InputBox("Input Column Name")
j = Cells(l, colname).Column
Set rng = Range(j & "1:" & j & Range(j & "1048576").End(xlUp).Row)
rng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In rng
If Application.WorksheetFunction.CountIf(rng, cel) > 1 Then
If Application.WorksheetFunction.CountIf(Range(j & "1:" & j & cel.Row), cel) = 1 Then
cel.Interior.ColorIndex = clr
clr = clr + 1
Else
cel.Interior.ColorIndex = rng.Cells(WorksheetFunction.Match(cel.Value, rng, False), 1).Interior.ColorIndex
End If
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub 

Here I used inputbox to capture user inputs. But I ended up in few errors. I get Run time error 1004, Application defined error Please help me to capture user inputs for select column. Thank You.

0

There are 0 answers