Highlite duplicates with different colours

[VbaHell]

Hello all

I am trying to modify this code from the excel forum to highlite dupplicate but with using a pop up box to specifly the column first

Keeps bugging out on Set myrng = Range(c)

Sub Find_Duplicate()
Dim cel As Variant
Dim myrng As Range
Dim clr As Long
Dim c As String
Dim LastRow As Long
Dim x As Long

c = InputBox("Enter column letter", "Remove Duplicates", "A")
If c = "" Then Exit Sub
LastRow = Range(c & Rows.Count).End(xlUp).Row


Set myrng = Range(c)
myrng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In myrng
If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
If WorksheetFunction.CountIf(Range("K2:K" & cel.Row), cel) = 1 Then
cel.Interior.ColorIndex = clr
clr = clr + 1
Else
cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
End If
End If
Next
End Sub

 

[Gary McMaster]

In your statement:

Set myrng = Range(c)

"c" is not a range. It is merely the string you entered in the input box. You must make it something like this:

Set myrng = Range(c & "2" & ":" & c & LastRow)

The number 2 being the starting row

Hope that helps.

Gary

 

[VbaHell]

Perfect thanks Gary

Regards

Gary