Hi Sudhir,
I hope the following code will help you out:
"Public Sub Test()
Dim W1 As Worksheet
Dim W2 As Worksheet
'Change the filw name accordingly
Set W1 = Workbooks("Old File.xls").Worksheets("Sheet1")
Set W2 = Workbooks("Latest File.xls").Worksheets("Sheet1")
For I = 2 To 200
Val1 = W2.Cells(I, 2).Value
For Each Cell In W1.Range("B2:B200")
If Cell.Value = Val1 Then
Ans = "Yes"
If Cell.Offset(0, 1).Value <> W2.Cells(I, 3).Value Then
W2.Cells(I, 3).Interior.ColorIndex = 6
End If
If Cell.Offset(0, 2).Value <> W2.Cells(I, 4).Value Then
W2.Cells(I, 4).Interior.ColorIndex = 6
End If
If Cell.Offset(0, 3).Value <> W2.Cells(I, 5).Value Then
W2.Cells(I, 5).Interior.ColorIndex = 6
End If
If Cell.Offset(0, 4).Value <> W2.Cells(I, 6).Value Then
W2.Cells(I, 6).Interior.ColorIndex = 6
End If
Exit For
Else
'
End If
Next
If Ans <> "Yes" Then
W2.Rows(I).Font.ColorIndex = 3
End If
Ans = ""
Next
For I = 2 To 200
Val1 = W1.Cells(I, 2).Value
For Each Cell In W2.Range("B2:B200")
If Cell.Value = Val1 Then
Ans = "Yes"
Exit For
End If
Next
If Ans <> "Yes" Then
W1.Rows(I).Interior.ColorIndex = 41
End If
Ans = ""
Next
End Sub
Put this code in a normal module. Some of the essential tips to use this
code are as follows:
1) This code evaluates 200 rows, if you want to evaluate more rows, change
this part of the cose "B2:B200"
2) First it evaluates the new file with the old and then old with the new
This is the first time i have composed a code for somebody, i hope it will
be up to the mark.
Please provide your feedback on the same
Cheers!
Harry
3) You will have to
On Wed, Apr 28, 2010 at 11:05 PM, sudhir kumar <sudhir.p...@gmail.com>wrote:
> Dear Friend,
> Thanks for your response, I attached two excel sheet here, one is 'old
> file' and other one is 'latest file'.
> Latest file is having the latest changes, now i want to compare both the
> file so that i will come to know that what are the changes in between them,
> what is added,deleted or changed
> For Example,I already highlighted the cell with some color
> contents with red colour is newly added in latest file
> content with yellow colour is changed in latest file
> content with blue colour is deleted from the latest file
> Now what i want is to know all these changes automatically with the help of
> macro.
> thanks
> --
> Regards-
> Sudhir Kumar
> Design Engineer,
> Conceptia Software Technologies Pvt. Ltd.
> Email id-sudhir.p...@gmail.com,
>
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
