اكسل

Within Each Row

This VBA code checks all the cells from a row and highlights all the cells which are duplicate within a row.

In simple words, if a row has the value “522” twice then it will be considered as duplicate. But if the another 522 is in another row then it will be considered as unique.

Sub DuplicateValuesFromRow()
'Declare All Variables.
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
'Count Number of Rows and Columns
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
'Loop Each Row To Check Duplicate Values and Highlight cells.
For i = 2 To myRow
Set myRange = Range(Cells(i, 2), Cells(i, myCol))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
End Sub

If you go through this macro, you will find that we have used a loop to check each row for duplicate values and highlight them with a color.