About Us

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:

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("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.

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.

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

Don Guillett

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.

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("D2:D3198")
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

"edluver" wrote:

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.

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.

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?

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

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

"edluver" wrote in message
...
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?

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IF function that searches a range of cells for data and provide tr	offdah3z	Excel Worksheet Functions	2	August 13th 09 07:46 PM
Getting valid web searches and avoiding sites that contaminate web searches	David McRitchie	New Users to Excel	1	June 13th 06 03:00 AM
Need help -- a Macro that searches for string in a cell range?	graphicGuy	Excel Programming	1	April 18th 06 05:53 PM
which formula or function searches for a value in a range of cell.	Roccobarocco	Excel Worksheet Functions	5	December 3rd 04 12:06 PM
which formula or function searches for a value in a range of cell.	Roccobarocco	Excel Worksheet Functions	7	December 3rd 04 09:06 AM

Menu

About Us