The following subroutine will highlight all the duplicate values in range in yellow. It does not matter whether the values are text or numbers. It uses Excel’s COUNTIF function to count up the duplicates and then sets the colour to yellow:

Sub Highlight_Duplicates(Values As Range)
Dim Cell

For Each Cell In Values
    If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
        Cell.Interior.ColorIndex = 6
    End If

Next Cell
End Sub

And we have invoked it using a the click event:

Private Sub CommandButton1_Click()
Highlight_Duplicates (Sheets("Sheet1").Range("C10:F14"))

End Sub

and then if we press the button we see all the duplicates:

To download the .XLS file with this tutorial, click here