Hi Dave,

thanks for Your reply. But it is not actually what I need to do:
I have chunks of numbers distributed over a worksheet which I grouped
with a named area. That data is not static, but will be overwritten
quite frequently (manually). Below an excerpt of some of the data:

21
105
264
438

113
212
217
105
166
183

166
183
217
21
113
212
217
438

What I need is a function which checks all the numbers in the named
range for duplicates and highlights those duplicates (not delete, not
clear, just highlite), preferably with different colors. Means, all
21's with red background, all 217's with yellow and so on.


Dave Peterson wrote:
I'm not quite sure what you're doing, but that list that you get from a
Edit|FindAll isn't available via code.

But it looks like you may be able to use format|Conditional formatting to
highlight your range for duplicates. But you're also clearing the cells that
contain the duplicate value.

I'd approach it something like:

Option Explicit
Sub MarkDuplicates()

Dim FoundCell As Range
Dim ValueToFind As Variant
Dim RngToCheck As Range
Dim CI As Long

CI = 3

With ActiveSheet
ValueToFind = ActiveCell.Value
'or be specific
'ValueToFind = .Range("a1").Value
Set RngToCheck = .Range("DataArea")
End With

If ValueToFind = "" Then
Exit Sub
End If

Do
With RngToCheck
Set FoundCell = .Cells.Find(what:=ValueToFind, _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
End With

If FoundCell Is Nothing Then
Exit Do 'no more to do
End If

With FoundCell
.ClearContents
.Interior.ColorIndex = CI
End With

Loop

End Sub



Pat wrote:

I've got a worksheet which contains several lists with numbers
(different ranges), which I want to verify and mark for duplicates.
Now if I use the 'find all' function from excel, the popup shows a list
of cells which correspond to the looked up value. I want to use that
result in my code to decide, if there is more than 1 match. But I have
no clue, on how to access that information. If I do the following 'IF -
THEN' statement (just a littel testversion - and for all you hard core
programmers out the I'm a bloody beginner, so please excuse if its
not state of the art), its checking for the looked up value rather than
the instances:

Sub MarkDuplicates()

Cycles = 600
CI = 3
OS = 0

Range("DataArea").Select
LV = ActiveCell()

For j = 1 To Cycles

If Selection.Find(What:=LV) 1 Then
Application.ReplaceFormat.Interior.ColorIndex = CI
Selection.Replace What:=LV, Replacement:="",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True
CI = CI + 1
End If

OS = OS + 1
LV = ActiveCell.Offset(OS, 0)

Next j

End Sub

Can You please help me out and let me know, how I can solve this task?
thanks in advance for any sugestions. :-)

--

Dave Peterson