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.