Previous Topic Next Topic

Help needed in using marcos

Solved/Closed
Asmodeux Posts 1 Registration date Sunday July 28, 2013 Status Member Last seen July 28, 2013 - Jul 28, 2013 at 11:48 AM
 Asmodeux - Jul 30, 2013 at 11:41 AM
Hi,

I need to write a marco that is able to compare the specific column in 2 different worksheets within the same workbook. (i.e Names of staffs)

The differences should be highlighted.

I have tried using the following codes but it didn't work.

Sub CompareSheets2()

Dim wbk As Workbook
Set wbk = ThisWorkbook

Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws2 = wbk.Sheets(2)
Set ws3 = wbk.Sheets(3)

Dim CheckRange As Range
Set CheckRange = ws2.Range("E2:E80")

Dim cell As Range

CheckRange.ClearFormats

For Each cell In CheckRange

If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then
cell.Interior.Color = RGB(200, 160, 35)
End If

Next cell

End Sub


Any advise ?

Thanks in advance for your answer!

Greatly appreciate the help with this issue.

4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 29, 2013 at 11:44 AM
Hi Asmodeux,

You are comparing ws2 with ws2.

So change the 2 into 3 in one of the two code lines:
Set CheckRange = ws2.Range("E2:E80")
or
If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then

Best regards,
Trowa
0
Answer 2 / 4
Asmodeux
Jul 30, 2013 at 08:51 AM
Thank man it worked ...

One more question if you don't mind helping me around...

How to do paste the difference into another worksheet ?

Greatly appreciate for your help
0
Answer 3 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 30, 2013 at 10:31 AM
Hi Asmodeux,

I'm here to help, so here we go.

It depends on which value you want to paste.

Change: 
If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then 
cell.Interior.Color = RGB(200, 160, 35) 
End If
Into: 
If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then
cell.copy Sheets("Your sheet name").range("A"&rows.count).end(xlup).offset(1,0)
cell.Interior.Color = RGB(200, 160, 35) 
End If
Or: 
If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then
ws2.Cells(cell.Row, cell.Column).copy Sheets("Your sheet name").range("A"&rows.count).end(xlup).offset(1,0) 
cell.Interior.Color = RGB(200, 160, 35) 
End If

Best regards,
Trowa
0
Answer 4 / 4
Asmodeux
Jul 30, 2013 at 11:41 AM
Thanks so much for ur prompt reply.

Appreciate it.

Cheers
0