Find red cell in macro

[Guest]

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

 

[David McRitchie]

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
--

 

[David McRitchie]

the reason is that your email is being wrapped at a particular column
either on your email or that of the sender some column ns wrapping the text.
The break will occur before that column so that words don't get split in half.

 

[Guest]

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]

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/condfmt.htm#duplicates
be sure to read the top part of the webpage and also look for uses
involving "duplicate" on the page.

 

[Guest]

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]

Hi Judy,
You could use a helper column

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

The you use Find (Ctrl+F) for 1