Rob,

Thanks!! It works great! I really appreciate your time,
and effort in sharing your knowledge. I'm going to take
the opportunity to learn VB... it's a long way from my
Cobol days back in the 70s! :)

Glenn


-----Original Message-----
Glenn,

The code supplied does the logic you want with respect to
categories and
products.

But, it expects products to start with an indent
character.
If the indent is actually a formatting feature and not a
character then the
code will fail.

If it's a formatting indent then you could make the
following changes to the
code:

If Not Left(strTemp, 1) = cTabChar Then
becomes
If wksS.Cells(i, 1).IndentLevel = 0 Then

Do Until Left(rngDest.Offset(j + 1, 0).Value, 1) <
cTabChar
becomes
Do Until rngDest.Offset(j + 1, 0).IndentLevel = 0


Rob


"Glenn" wrote in
message
...
Rob,

This is great! Thanks! I'm going to have to take a
long
look at it and give it a try, but I wanted to take a
second and thank you.

There isn't any "real" indent in the data -- it's
formatted that way and I probably shouldn't have said
that. My bad.

Regarding the categories: in my example below,
product "cat" needs to be tested in Category ABCDEF-
53426334 from List #1 (sheet1) against the existence
of "cat" in the same category in List #2 (sheet2).

The test for product existence is always and strictly
within the same category. IOW, first you test the
existence of a category and then test for existence of
products within that category. Then move on to the next
category, etc. etc.

Thanks again for taking the time!

Glenn






-----Original Message-----
Glenn,

I notice that your indent character is not a space but
Chr
(160). Depending
on how newsgroups mangle this message, you may need to
change cTabChar back
to Chr(160)

Wasn't sure what you were talking about "match-tested
within the same
category". I'm assuming you meant Product, not
Category.

Also assuming Product and Category never match (mainly
because of the
Indent)


Sub test()
Const cTabChar = " ", cColourIndex = 35

Dim lngLastRow As Long, i As Long, j As Long,
rngDest
As Range, rng As
Range
Dim wksS As Worksheet, wksD As Worksheet
Dim strTemp As String

Set wksS = Sheet1
Set wksD = Sheet2

lngLastRow = wksS.Cells(Rows.Count, 1).End
(xlUp).Row

i = 1
Do Until i lngLastRow
strTemp = wksS.Cells(i, 1).Value
If Not Left(strTemp, 1) = cTabChar Then
Set rngDest = wksD.Columns(1).Find(strTemp,
LookIn:=xlValues,
LookAt:=xlWhole, SearchDirection:=xlNext)
If Not rngDest Is Nothing Then
j = 0: Do Until Left(rngDest.Offset(j +
1, 0).Value, 1) <
cTabChar: j = j + 1: Loop
If j 0 Then Set rngDest = Range
(rngDest.Offset(1, 0),
rngDest.Offset(j, 0))
Else
wksS.Cells(i, 1).Interior.ColorIndex =
cColourIndex
End If
Else
If Not rngDest Is Nothing Then
Set rng = rngDest.Find(strTemp)
If rng Is Nothing Then wksS.Cells(i,
1).Interior.ColorIndex
= cColourIndex
End If
End If
i = i + 1
Loop
End Sub


Rob


"Glenn" wrote in
message
...
Hello,

I haven't touched code in 10+ years and I need to do
this
quick thing and... errr... could someone just flesh
it
out
a bit for me and I'll do the rest of the legwork?
Maybe
just some help with the syntax and looping
statements?
It
would be much appreciated.

I have two LONG single-column lists, each one on it's
own
spreadsheet tab. There are product categories with
indented product listings under each category. The
categories all start with the same 6 characters (say
ABCDEF) and then a bunch more characters. Starting
at
the
top of one worksheet, I want to go down 1 item at a
time
and see if it's in the 2nd list. If it ISN'T, I
want to
color that entry or flag it in some way.

ABCDEF-53426334
bat
cat
dog
mouse
ABCDEF-827726363
armadillo
cat
ferret

Everything's in alphanumeric order -- categories and
products -- and the categories are unique and won't
repeat, but the products might. The test has to be
correct for the given category, so 'cat' has to be
tested
twice in the case above, and match-tested within the
same
category on the other sheet.

Anyway, any and all help would be appreciated. Even
guidance to an article or link to a similar exercise.

Thanks in advance!
Glenn



.



.