Help with Macro that searches a Range for specified information

[edluver]

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:

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) Then
Rows("OrigRow:OrigRow").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
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.

 

[Tom Ogilvy]

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("D23198")
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

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:

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.

 

[edluver]

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.

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("D23198")
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

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:

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.

 

[Don Guillett]

try
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
(e-mail address removed)

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.

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("D23198")
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

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:

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.

 

[Tom Ogilvy]

I assumed your account number list was contiguous (no blank cells
intermixed). If not, then this should work. To demonstrate, select G2 and
hit end, then the down arrow. This probably goes to the bottom of the 1st
quarter. In contrast, now go to the bottom of G or well past your data and
do End and then up arrow. This is what it does not. If it selects
information that you don't want processed, then you will have to provide the
rule for what to exclude. I am not sure this is a problem because if there
is not a match in one of the lists, it won't shade the row anyway.

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("D23198")
with Sheets("ASI-19 ActiveSheet")
.rows.Interior.ColorIndex = xlNone
set rng = .range(.Cells(2,7),.Cells(rows.count,7).End(xlup))
End with
for each cell in rng
if not isempty(cell) then
if application.countif(rng1,cell)>0 or _
application.countif(rng2,cell) > 0 then
cell.EntireRow.Interior.ColorIndex = 6
end if
end if
Next
end sub

--
Regards,
Tom Ogilvy

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.

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("D23198")
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

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:

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.

 

[edluver]

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

try
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
(e-mail address removed)

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.

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("D23198")
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


:

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:

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.

 

[edluver]

at the risk of overstepping my welcome, i did have one more question i was
hoping you could answer for me. in the re-worked code, there is a variable
"cell" defined as a range, but the only time i see it used is in the "For
Each" statement, and i dont understand how it is used to match the
information if it is never defined, would you be willing to help me out one
more time?

try
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
(e-mail address removed)

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.

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("D23198")
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


:

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:

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.

 

[Don Guillett]

It could have been named anything such as
for each ed
for each xxx
for each i
for each pepperonipizza
as long as you are consistent where it is used.
--
Don Guillett
SalesAid Software
(e-mail address removed)

at the risk of overstepping my welcome, i did have one more question i was
hoping you could answer for me. in the re-worked code, there is a
variable
"cell" defined as a range, but the only time i see it used is in the "For
Each" statement, and i dont understand how it is used to match the
information if it is never defined, would you be willing to help me out
one
more time?

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
(e-mail address removed)

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.

:

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("D23198")
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


:

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:

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.