Hi Judy,
You could use a helper column

B32: =COUNTIF($A$1:$A1,$A1)

The you use Find (Ctrl+F) for 1
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Judy Ward" wrote in message ...
OK, but if I switch to Conditional Formatting I still want a macro to find
the first cell that is formatted as a duplicate (so I don't have to visually
look through 2000 cells). The reason I want a macro instead of just using
the Find command is because if there aren't any duplicates, I want the rest
of my macros to continue formatting the spreadsheet. I can't come up with a
macro to find a cell that is formatted with a fill color. Can you point me
towards help for the Find?

Thank you,
Judy

"David McRitchie" wrote:

Hi Judy,
You are checking the active cell for a null string value rather than
including the offset count for comparison. I didn't really check all
of the code.

But you can do this a lot better with Conditional Formatting.
http://www.mvps.org/dmcritchie/excel...htm#duplicates
be sure to read the top part of the webpage and also look for uses
involving "duplicate" on the page.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Judy Ward" wrote in message ...
I think my subject line misled you.

I have a macro that looks for duplicates and turns the cells red on purpose.
Because I have over 2,000 rows in my Excel file, I don't want to scroll
through looking for the red cells. I want the macro that marks the
duplicates to search for the cells it marked as duplicates. I can do this by
manually using the Find command, but I would like to do it in a macro. The
macro I tried gives an error.

Here is the code that marks the duplicates (this works):
' Must select the first cell in the column
Range("C2").Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop

Now I am looking for code to find the duplicates (formatted in red).

Thank you,
Judy

"David McRitchie" wrote:

Red indicates a syntax error, since you copied the code from another source
such as a posting, it is very simple to fix recombine the line that continues
and does not end with a space underscore

the 3 lines you have should probably appear as follows:
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:=False _
, SearchFormat:=True).Activate
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Judy Ward" wrote in message ...
I have a "Find_Duplicates" macro that turns the fill color red for cells that
are a duplicate of the preceding row.

Now what I need is a macro that searches for these cells with the red fill
color. I tried to record the steps of the find and got:
With Application.FindFormat.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate

This doesn't work as a macro. I get this error message:
Run-time error '91':
Object variable or With block variable not set.

And if I can get this "find" working, I need to know how to recover if there
is nothing to find.

I appreciate any help you can give me.
Thank you, Judy