Find and Highlight

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ranjan.khan@comcast.net - 28 Apr 2007 16:17 GMT

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.

Reply to this Message

Barb Reinhardt - 28 Apr 2007 16:54 GMT

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

> I have two sheets called Sheet1 and Sheet2 in WKBK
>
[quoted text clipped - 25 lines]
>
> Thanks.

Reply to this Message

ranjan.khan@comcast.net - 28 Apr 2007 20:02 GMT

On Apr 28, 11:54 am, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:

> Try this:
>
[quoted text clipped - 68 lines]
>
> > Thanks.

Sweet! That works. Thanks!

Reply to this Message

anon - 28 Apr 2007 16:57 GMT

>I have two sheets called Sheet1 and Sheet2 in WKBK
>
[quoted text clipped - 23 lines]
>
>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?

Reply to this Message

ranjan.khan@comcast.net - 28 Apr 2007 16:59 GMT

> >I have two sheets called Sheet1 and Sheet2 in WKBK
>
[quoted text clipped - 26 lines]
> 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

Reply to this Message