hi guys
i'm trying to find duplicates in 2 sheets. sheet1 has one column with unique references and sheet2 has these unique references throughout the whole sheet over many columns - when I find the duplicates in sheet2 I want to colur the cell. IF the data was numbers I can find them no problem with the code below. however the data is made up of numbers and text and I keep getting a data mismatch error. I don't know why. if I use conditional formatting then this finds the duplicates and highlights them but for reasons I don't want to go into here i'm not going to use this method.
can any one see what is wrong with the code below?? the data mismatch comes up on the line If CELL1.Value = CELL2.Value Then
any help appreciated
i'm trying to find duplicates in 2 sheets. sheet1 has one column with unique references and sheet2 has these unique references throughout the whole sheet over many columns - when I find the duplicates in sheet2 I want to colur the cell. IF the data was numbers I can find them no problem with the code below. however the data is made up of numbers and text and I keep getting a data mismatch error. I don't know why. if I use conditional formatting then this finds the duplicates and highlights them but for reasons I don't want to go into here i'm not going to use this method.
can any one see what is wrong with the code below?? the data mismatch comes up on the line If CELL1.Value = CELL2.Value Then
VBA Code:
Dim rng1, rng2, CELL1, CELL2 As Range
Set rng1 = Worksheets("SHEET1").Range("D2:BV300")
'rng1 defines the existing data in column B and worksheet1
Set rng2 = Worksheets("READS_REQUIRED").Range("K:K")
'rng2 defines the imported data in column D and worksheet2
For Each CELL1 In rng1
If IsEmpty(CELL1.Value) Then Exit For
For Each CELL2 In rng2
If IsEmpty(CELL2.Value) Then Exit For
'ERROR COMES UP HERE'
If CELL1.Value = CELL2.Value Then
'compare data in cell1 and cell2 and then format if they have equal values.
CELL1.Interior.ColorIndex = 6
End If
'run the looping process
Next CELL2
Next CELL1
any help appreciated