MrExcel.com - Your One Stop for Excel Tips & Solutions

HELP - NEED FIND AND FIND NEXT MACRO

Posted by EGRIS on October 07, 2001 2:57 PM

I NEED A MACRO THAT SIMPLIFIES THE BELOW REPETITIVE MACRO. I HAVE A SHEET WHERE I NEED TO FIND AND HIGHLIGHT APPROXIMATELY 100 DIFFERENT CELLS IN COLUMN "A" WHICH HAS 1000 CELLS OF DATA. IS THERE A SIMPLER WAY OF DOING THIS RATHER THAN REPEATING THE FIND MACRO THAT I RECORDED WITH THE MACRO RECORDER BELOW:

Cells.Find(What:="WHATEVER", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Interior.ColorIndex = 6

Cells.Find(What:="WHOEVER", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Interior.ColorIndex = 6

ETC. ETC.

Posted by Zoltan Czibor on October 07, 2001 4:36 PM


Here's one way :-

Dim rng As Range
Set rng = Range(Range("A1"), Range("A65536").End(xlUp))
Application.ScreenUpdating = False
rng.EntireColumn.Insert
With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(OR(RC[1]=""WHATEVER"",RC[1]=""WHOEVER""),1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).Offset(0, 1).Interior.ColorIndex = 6
.EntireColumn.Delete
End With


Posted by Zoltan Czibor on October 07, 2001 4:47 PM

Also .....


This could also be done without a macro by Conditional Formatting.

Posted by EGRIS on October 07, 2001 5:46 PM

I GET ERROR MESSAGE WITH YOUR SUGGESTION

APPLICATION DEFINED OBJECT DEFINED ERROR MESSAGE COMES UP WITH THIS MACRO

Posted by Zoltan Czibor on October 07, 2001 11:27 PM

Re: I GET ERROR MESSAGE WITH YOUR SUGGESTION

I've just tested the macro and it works for me.
However, in case there are no cells containg "WHATEVER" or "WHOEVER", use this instead :-

Dim rng As Range
Set rng = Range(Range("A1"), Range("A65536").End(xlUp))
Application.ScreenUpdating = False
rng.EntireColumn.Insert
With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(OR(RC[1]=""WHATEVER"",RC[1]=""WHOEVER""),1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 1).Offset(0, 1).Interior.ColorIndex = 6
On Error GoTo 0
.EntireColumn.Delete
End With

However, I don't think that this will remove the error message that you describe. What line is causing the error message and what is the exact wording of the message?