GS wrote on 04/12/2011 15:47 ET :
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

Thanks a lot, I tried macro several times. Column 3 in two workbook (week1
and
week2) excel are highlighted. However, data of column 3 in week 1 excel is
deleted and no data copied on week 2 excel column 3. It may be some
macrostatments is missing.

Secondly, how can I modified the macro statement, therefore, it can applied on
specific sheet?
For example, in week 1 excel, there are 2 sheets (west canada and east
canada).
Week 2 excel also have 2 sheet.
As I would like to compare week 1 excel sheet 2 with week 2 excel sheet 2
(both
are column 3). Which macro statement should I change?

I can attach two excel file to let you have a look. How can I attach file
here?
Thanks.