Dear Gary,

Thanks a lot. I wonder if I can ask you one more thing.

You're welcome. Glad you were able to get it sorted out about replies
to one thread per topic.


For example, the matching column changed from 4 to 1, should I modify the
statement: Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")

I have revised the sub (see below) to work with data in ColA and
comments in ColC.


At this time, I would like to copy column 3 data (no adjacent to column 1)
from
old week 1 excel to new week 2 excel (also column 3). After that , I would
like
to cell fill in yellow colour. What should I add macro statement? I tried
several times, please teach me. Thanks


Would you mind tell interpet / explain the macro for this type:
For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then _
rng.Offset(, 1) = rngSource.Cells(lRow).Offset(, 1)
Next

The above code is what's known as a 'Loop' structure that uses a
counter for each iteration. In this case, it's checking in rngTarget
for a match to each cell in rngSource starting in row1 and ending in
the last row containing data in that column. If it finds a match then
it copies the comment in rngSource to the appropriate cell in
rngTarget.


<Revised code
Sub CompareData()
Dim rngSource As Range, rngTarget As Range, rng As Range
Dim lLastRow As Long, lRow As Long

Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Row

For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then
With rngSource.Cells(lRow).Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
rng.Offset(, 2).Interior.ColorIndex = 6
End If
Next
End Sub

***
The above will 'flag' the comment cells yellow. If you only want to
flag the matched cells then replace the If..Then construct with the
following:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Offset(, 2).Value = rng.Offset(, 2)
.Interior.ColorIndex = 6
End With
rng.Interior.ColorIndex = 6
End If

***
If you want to 'flag' both cells on each sheet:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Interior.ColorIndex = 6
With .Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
End With
With rng
.Interior.ColorIndex = 6
.Offset(, 2).Interior.ColorIndex = 6
End With
End If

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc