...Calling all VB Experts

[Noir]

The purpose of the code below is to look at a series of serial numbers within my workbook, column A, sheet1 and match them to a series of serial numbers listed in column A, sheet2 of the same workbook. If the code finds a serial number that is located on both sheets, it is supposed to highlight the serial number cell in Column A, sheet1. The problem is, the code will only highlight down column A until if finds a serial number not located on sheet2. At that point the code stops highlighting cells.

How can i make it so the code will highlight the found serial numbers while skipping over the serial numbers it didn't find?

Thx,
Noir



Sub FindDuplicates()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range([A2], [A65536].End(xlUp)).Offset(0, 2).Formula = "=IF(COUNTIF(Sheet2!RC[-2]:Sheet2!R[2998]C[-2],RC[-2])<>0,""Yes"",""No"")"
Dim theCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set theCol = Range(Range("C2"), Range("C65536").End(xlUp))
LtoSel = "Yes"
For Each cell In theCol
If Right(cell, 3) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
On Error GoTo e
RtoSel.Offset(0, -2).Select
With Selection
.Font.FontStyle = "Bold"
.Interior.ColorIndex = 18
End With
Range([C2], [C65536].End(xlUp)).clear
[A1].Select
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "There are no duplicates.", 64, "CONGRATULATIONS !"
[A1].Select
End Sub

[dimrod]

Your code to enter the formula should be :-

Range([A2], [A65536].End(xlUp)).Offset(0, 2).Formula = "=IF(COUNTIF(Sheet2!R2C1:Sheet2!R3000C1,RC[-2])<>0,""Yes"",1)"


Also, the code could be simplified a bit :-

Sub FindDuplicates()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range([A2], [A65536].End(xlUp)).Offset(0, 2).Formula = "=IF(COUNTIF(Sheet2!R2C1:Sheet2!R3000C1,RC[-2])<>0,""Yes"",1)"
On Error GoTo e
With Columns(3).SpecialCells(xlCellTypeFormulas, 2).Offset(0, -2)
.Font.FontStyle = "Bold"
.Interior.ColorIndex = 18
End With
Columns(3).Clear
[A1].Select
Exit Sub
e:
MsgBox "There are no duplicates.", 64, "CONGRATULATIONS !"
On Error GoTo 0
Columns(3).Clear
[A1].Select
End Sub

[Noir]

Dimrod,
Thanks for your reply!

I may be doing something wrong but, the code is doing the exact same thing. It only highlights a portion of the serial numbers.

Also, what does, "Your code to enter the formula should be
Range([A2], [A65536].End(xlUp)).Offset(0, 2).Formula = "=IF(COUNTIF(Sheet2!R2C1:Sheet2!R3000C1,RC[-2])<>0,""Yes"",1)" mean? Did i need to do something special with this line of code? I just took the long code and placed it into a standard module. Did i need to place it somewhere else?

Thx,
Noir

[Nimrod]

This is a modification of the VBE help example for the find command. I've modified it so that it looks at values on Sheet 2 then highligts the same value on sheet1...if found there.
Does this help ?

[Nimrod]

This is a modification of the VBE help example for the find command. I've modified it so that it looks at values on Sheet 2 then highligts the same value on sheet1...if found there.
Does this help ?

Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(2).Cells(65536, 1).End(xlUp).Row)
  SrchValue = Worksheets(2).Cells(RwCnt, 1).Value
        If Len(Trim(SrchValue)) > 0 Then
        With Worksheets(1).Range("a1:a" & (Cells(65536, 1).End(xlUp).Row))
            Set c = .Find(SrchValue, LookIn:=xlValues, Lookat:=xlWhole)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Interior.ColorIndex = 6
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
        End If
Next RwCnt
End Sub

[Noir]

Yes, the new version worked GREAT!!

Thanks Nimrod!
Noir

[dimrod]

On 2002-05-22 09:22, Noir wrote:
Dimrod,
Thanks for your reply!

I may be doing something wrong but, the code is doing the exact same thing. It only highlights a portion of the serial numbers.

Also, what does, "Your code to enter the formula should be
Range([A2], [A65536].End(xlUp)).Offset(0, 2).Formula = "=IF(COUNTIF(Sheet2!R2C1:Sheet2!R3000C1,RC[-2])<>0,""Yes"",1)" mean? Did i need to do something special with this line of code? I just took the long code and placed it into a standard module. Did i need to place it somewhere else?

Thx,
Noir

Glad to see you got some code that does what you want.

However, just for the record, your macro works with the amendment to the line that enters the formula.

Can't think what you were doing wrong.

If you want to see what the amendment did, step through the code before and after the amendment, and have a look at the formula on the worksheet.