find duplicates

[oamram]

hi all,
new to this, i am trying to write a macro loop through the column and color all duplicates of cells. i first sort the column then i want it to loop through and color all the duplicate(except the first one) in red. that is the code i have so far it does not seems to work well.

Sub Macro1()
Dim Count As Integer
Dim Previous As Integer
Dim range As Integer
Dim curCell As Integer
Dim nextCell As Integer
Dim lastCell As Integer

curCell = 1
nextCell = 2
lastCell = 3

Count = 2
range = 10
Previous = Count - 1

Do While Count < range
If StrComp(Cells(Previous, curCell), Cells(Count, curCell), vbTextCompare) = 0 Then
Cells(Count, curCell + 1).Interior.Color = RGB(255, 0, 0)

Else:

Cells(Previous, curCell + 1).Interior.Color = RGB(255, 67, 0)

End If
Count = Count + 1
Loop
End Sub


Thanks alot, Oamram.

[lucas]

Moved to the appropriate forum.

[Bob Phillips]

Code results seem odd, but is this what you want?

[vba]

Sub Macro1()
Dim Count As Integer
Dim Previous As Integer
Dim range As Integer
Dim curCell As Integer
Dim nextCell As Integer
Dim lastCell As Integer

curCell = 1
nextCell = 2
lastCell = 3

Count = 2
range = 10

Do While Count < range
Previous = Count - 1
If StrComp(Cells(Previous, curCell), Cells(Count, curCell), vbTextCompare) = 0 Then

Cells(Count, curCell + 1).Interior.Color = RGB(255, 0, 0)
Else

Cells(Previous, curCell + 1).Interior.Color = RGB(255, 67, 0)
End If
Count = Count + 1
Loop
End Sub
[/vba]

[oamram]

Thanks man, it works, do you mind giving a quick explanation on the changes you made.

Thanks, oamram.

[mdmackillop]

Have a look at this

[Bob Phillips]

I just moved the line

[vba]

Previous = Count - 1
[/vba]

from outside of the Do Loop, to inside at the start.