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



.