VBA Code macro for duplicate entries

K

KCG

Hello friends,

I have my data in the range J14:AA14.

Please help me to create a VBA macro which will find duplicate entries in
this range and colour both cells red.

Thanx
 
G

Gary''s Student

Select your range and run:

Sub DupFinder()
Dim r As Range, t As Range
Set t = Selection
For Each r In t
v = r.Value
If Application.WorksheetFunction.CountIf(t, v) > 1 Then
r.Interior.ColorIndex = 3
End If
Next
End Sub

This is a general routine based on Selection. If you don't want to have to
Select then replace:

Set t = Selection
with:
Set t = Range("J14:AA14")
 
K

KCG

Hello Gary's Student,

I tried your suggestion and it worked fine. I have now changed the range to
J14:AA16.

I also have the following macro which I would like to merge with your
suggestion.
(This macro 'kicks in' every 16 rows automatically when I run it):

Public Sub ProcessData(StartRow)

For i = 10 To 14
For j = (StartRow + 1) To (StartRow + 1)
For k = 10 To 27
For L = (StartRow + 2) To (StartRow + 15)
If Cells(j, i).Value = Cells(L, k).Value Then
Cells(L, k).Font.ColorIndex = 38
End If
Next
Next
Next
Next

End Sub
--------------------------------------------------------------------------------------
Public Sub ProcessFile()
sectionRows = 16
startRows = 3
startCheckRow = 4
canContinue = (Cells(startCheckRow, 2).Value > 0)

While canContinue
ProcessData (startRows)
startRows = startRows + sectionRows
startCheckRow = startCheckRow + sectionRows
canContinue = (Cells(startCheckRow, 2).Value > 0)
Wend
End Sub


How do I merge the two macros? Thanx for your help.
 
Top