Find and Highlight

[ranjan.khan]

I have two sheets called Sheet1 and Sheet2 in WKBK

Each sheet has only one column (column A) with content.

I want to be able to compare both sheets to see if the content in
their respective Column A match. If there is a number in
Sheet2 that doesn't match the number in Sheet1, then I want to
highlight with yellow those cells in Sheet2.

For example here is the content of both workbooks:

Sheet1 Sheet2
23 66
15 23
66 19
24 24
16
15

Notice that Sheet2 has 2 more different numbers in its column. 16 and
19

What I want to do now is to highlight those 2 cells with yellow.

NOTE: Sheet1 will always be a subset of Sheet2.

Can you provide a VBA code for this?

Thanks.

 

[Guest]

Try this:

Sub Test()
Dim myRange1 As Range
Dim myRange2 As Range
'Set range for Sheet1

Set myRange1 = Worksheets("Sheet1").Cells(2, 1)
lrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set myRange1 = myRange1.Resize(lrow - myRange1.Row + 1, 1)

'Set range for Sheet2

Set myRange2 = Worksheets("Sheet2").Cells(2, 1)
lrow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Set myRange2 = myRange2.Resize(lrow - myRange2.Row + 1, 1)

For Each r1 In myRange1
r1.Interior.ColorIndex = 6
For Each r2 In myRange2
Debug.Print r1.Value, r2.Value
If r2.Value = r1.Value Then
r1.Interior.ColorIndex = xlColorIndexNone
End If
Next r2
Next r1

For Each r2 In myRange2
r2.Interior.ColorIndex = 6
For Each r1 In myRange1
Debug.Print r1.Value, r2.Value, Len(r1.Value), Len(r2.Value)

If r2.Value = r1.Value Then
r2.Interior.ColorIndex = xlColorIndexNone
End If
Next r1
Next r2
End Sub

HTH,
Barb Reinhardt

 

[anon]

I have two sheets called Sheet1 and Sheet2 in WKBK

Each sheet has only one column (column A) with content.

I want to be able to compare both sheets to see if the content in
their respective Column A match. If there is a number in
Sheet2 that doesn't match the number in Sheet1, then I want to
highlight with yellow those cells in Sheet2.

For example here is the content of both workbooks:

Sheet1 Sheet2
23 66
15 23
66 19
24 24
16
15

Notice that Sheet2 has 2 more different numbers in its column. 16 and
19

What I want to do now is to highlight those 2 cells with yellow.

NOTE: Sheet1 will always be a subset of Sheet2.

Can you provide a VBA code for this?

Yes, but conditional formatting would be a lot easier. Is there a
reason why you do not wish to use conditional formatting?

 

[ranjan.khan]

Yes, but conditional formatting would be a lot easier. Is there a
reason why you do not wish to use conditional formatting?

Conditional formatting is okay. I just want to be know to be able to
make a note on what new items are in Sheet2

 

[ranjan.khan]

Try this:

Sub Test()
Dim myRange1 As Range
Dim myRange2 As Range
'Set range for Sheet1

Set myRange1 = Worksheets("Sheet1").Cells(2, 1)
lrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set myRange1 = myRange1.Resize(lrow - myRange1.Row + 1, 1)

'Set range for Sheet2

Set myRange2 = Worksheets("Sheet2").Cells(2, 1)
lrow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Set myRange2 = myRange2.Resize(lrow - myRange2.Row + 1, 1)

For Each r1 In myRange1
r1.Interior.ColorIndex = 6
For Each r2 In myRange2
Debug.Print r1.Value, r2.Value
If r2.Value = r1.Value Then
r1.Interior.ColorIndex = xlColorIndexNone
End If
Next r2
Next r1

For Each r2 In myRange2
r2.Interior.ColorIndex = 6
For Each r1 In myRange1
Debug.Print r1.Value, r2.Value, Len(r1.Value), Len(r2.Value)

If r2.Value = r1.Value Then
r2.Interior.ColorIndex = xlColorIndexNone
End If
Next r1
Next r2
End Sub

HTH,
Barb Reinhardt

Sweet! That works. Thanks!