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