Previous Topic Next Topic

Help needed in using marcos

Solved/Closed
Asmodeux Posts 1 Registration date Sunday 28 July 2013 Status Member Last seen 28 July 2013 - 28 Jul 2013 à 11:48
 Asmodeux - 30 Jul 2013 à 11:41
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

Réponse 1 / 4
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
29 Jul 2013 à 11:44
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
Réponse 2 / 4
Asmodeux
30 Jul 2013 à 08:51
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
Réponse 3 / 4
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
30 Jul 2013 à 10:31
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
Réponse 4 / 4
Asmodeux
30 Jul 2013 à 11:41
Thanks so much for ur prompt reply.

Appreciate it.

Cheers