ExcelBanter - View Single Post - Help with Macro that searches a Range for specified information

edluver

Thank you very much. That seems to work very well. I appreciate all of your
time!

"Don Guillett" wrote:

try
set rng = .range(.Cells(2,7),.Cells(2,7).End(xldown))

lr=cells(rows.count,7).end(xlup).row
set rng = .range(.Cells(2,7),.Cells(lr,7).End(xlup))

or
set rng=range("g2:g" & cells(rows.count,"g").end(xlup).row))

--
Don Guillett
SalesAid Software

"edluver" wrote in message
...
This works very well, except it doenst seem to go through the entire
report.
Is there something i am supposed to be doing, or a particular format that
is
needed for this code to execute properly? it only seems to go through the
first quarter of the report and stops. But thank you very much, this is
much
closer to what i am looking for than i could ever get on my own. I really
appreciate you taking the time to help.

"Tom Ogilvy" wrote:

Sub MarkList()
Dim rng1 as Range, rng2 as Range
Dim rng as Range, cell as Range
set rng1 = Worksheets("Hotlist").Columns(2)
set rng2 = Worksheets("OCList").Range("D2:D3198")
with Sheets("ASI-19 ActiveSheet")
set rng = .range(.Cells(2,7),.Cells(2,7).End(xldown))
End with
for each cell in rng
if application.countif(rng1,cell)0 or _
application.countif(rng2,cell) 0 then
cell.EntireRow.Interior.ColorIndex = 6
end if
Next
end sub

would be the approach I would try. I may have misinterpreted you code,
of
course.

--
Regards,
Tom Ogilvy

"edluver" wrote:

I am trying to write a macro for my job that will take account numbers
from a
computer generated report and compair them with account numbers known
to have
problems. IF working properly, the macro would take the first account
number
on the "ActiveWorksheet" and compair it with the entire range of
account
numbers on the "Hotlist" and "OCList" and format the entire
corresponding
line on the "ActiveWorkSheet" if there is a "hit", then, move on to the
next
account number on the unsorted "ActiveWorkSheet" and repeat the process
until
complete. I am new at writing Macro's and thought this would be a
fairly
straight forward, simple macro, but i have had a terrible time and very
little success. This is a copy of the code:

Code:

Sub Asi19_Formatting_Macro() ' 'Asi19_Formatting_Macro Macro 'Macro written 2/17/07 by Edward S. Lane ' 'Set Pointers Dim OrigRow As Integer Dim HotListAmount As Integer OrigRow = 2 'Where to start compairing numbers HotListAmount = 1 'Will be used to determine the amount of items in the hotlist and subsequent range (I'm assuming there will be at least one). 'Finds the amount of itmes in the Hotlist to be used as the range Do While IsNumeric(Cells(OrigRow, 2)) Sheets("HotList").Select If IsNumeric(Cells(OrigRow, 2)) Then HotListAmount = OrigRow End If OrigRow = OrigRow + 1 Sheets("HotList").Select Loop OrigRow = 2 'Compare OCList account numbers Sheets("ASI-19 ActiveSheet").Select Do While IsNumeric(Cells(OrigRow, 7)) Sheets("ASI-19 ActiveSheet").Select If IsEmpty(Cells(OrigRow, 7)) = False Then If WorksheetFunction.Match(ActiveSheet.Range(OrigRow, 7), Worksheets("OCList").Range("D2:D3198").Select) ThenColorIndex = 6 .Pattern = xlSolid Rows("OrigRow:OrigRow").Select With Selection.Interior . End With End If End If OrigRow = OrigRow + 1 Sheets("ASI-19 ActiveSheet").Select Loop End Sub

this is only for the "OCList" with a known range. The first set of
code is
used to find the range of the "Hotlist" since it is dynamic and changes
daily. What am i doing wrong? I have gotten a wide range of errors
while
working on this, but the most common of those has been a mysterious
"400"
error with a big red X, and i have no idea what that means. I am sure
i have
many issues, redundent code and so forth, but to say i am frustrated is
to do
a disservice to the word. Any feedback and help with this would be
GREATLY
appreciated. I have posted this once before, but it was deleted, so if
you
replied to the previous one, i appologize and would like to thank you
for
your audience and help.