I am trying to find duplicate values in an excel sheet from multiple columns (via VBA / Macros)

It works for the most part, however, there are a few ambiguities when I am doing a comparison between numeric values. Numbers need to be compared literally rather than the actual value.

Attached is an excel sheet.

## 1.

Rows 2 and 4 are correctly identified as duplicates.

## 2.

Rows 6 and 9 are WRONGLY identified as duplicates.

## 3.

Rows 7 and 8 are CORRECTLY identified as NOT duplicates. When we have a some value in Column D (which may or may not contain any values)

## 4.

Rows 10 and 11 are WRONGLY identified as duplicates inspite of having unique values (atleast on display)

Any help is appreciated to resolve the inconsistency.

Duplicate.xlsm
Dim RowCount As Integer

Dim RowCounter As Integer

Dim DupRow As Variant

Dim DuplicateErrorDetails As Variant

Dim r As Range

Dim i As Long

Dim DuplicateErrorCount As Variant

DuplicateErrorCount = 0

DuplicateErrorDetails = ""

ActiveSheet.Range("K4").Cl

ActiveSheet.Range("K4").Cl

RowCount = ActiveSheet.Range("B:B").E

Columns("B:B").Select

Selection.NumberFormat = "@"

Set r = Range("H1").End(xlDown).Of

For RowCounter = 2 To r.Row

Range("A" & RowCounter).Interior.Color

Next

For RowCounter = 2 To r.Row

Range("I" & RowCounter).Value = ""

For i = RowCounter + 1 To r.Row

If Range("H" & RowCounter).Text = Range("H" & i).Text Then

DupRow = DupRow + 1

Range("A" & i).Interior.ColorIndex = 31

End If

Next

If DupRow >= 1 Then

DuplicateErrorCount = DuplicateErrorCount + 1

' Range("A" & RowCounter).Interior.Color

DuplicateErrorDetails = DuplicateErrorDetails & "Duplicate values found in row " & RowCounter & vbLf

DupRow = 0

End If

Next

If (DuplicateErrorDetails = "") Then

DuplicateErrorDetails = "No Error"

End If

ActiveSheet.Range("K4").Ho

ActiveSheet.Range("K4").Va

ActiveSheet.Range("K4").Ad

DuplicateErrorCount = 0

DuplicateErrorDetails = ""

End Sub