PDA

View Full Version : find duplicates



oamram
10-26-2008, 09:40 AM
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
10-26-2008, 12:38 PM
Moved to the appropriate forum.

Bob Phillips
10-26-2008, 12:48 PM
Code results seem odd, but is this what you want?



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

oamram
10-26-2008, 01:31 PM
Thanks man, it works, do you mind giving a quick explanation on the changes you made.

Thanks, oamram.

mdmackillop
10-26-2008, 01:59 PM
Have a look at this (http://www.xldynamic.com/source/xld.DupMaster.html)

Bob Phillips
10-26-2008, 02:06 PM
I just moved the line



Previous = Count - 1


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