Data mismatch searching for duplicates on two sheets

[9davros8]

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

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

 

[Fluff]

Make sure that none of the cells you are looking at contain errors such as #N/A, #VALUE!, #NAME? etc

 

[9davros8]

hi
thanks Fluff. did not know that. I did have some #N/A's in my data. My codes still not working but at last I've got rid of one problem

thanks again

 

[Fluff]

In what way is it "still not working"?

 

[9davros8]

wow - my mistake. I ran it this morning and nothing happened. I thought I'd run it now before I post back and it worked.
very slow though - takes around 3 minutes to find all the duplicates. still as long as it works.
now I've got this working my next goal is to offset the cell colour to the cell to the left. still i'm getting their slowly
thanks fo your help

 

[Fluff]

Glad it's sorted & thanks for the feedback