Find and match - Highlight Duplicates in 2 Columns

[IKHAN]

Trying to highlight with different colors for duplicate cells in excel 2010 across column D and E finding and matching date and time (Custom formated cells ddd mmm dd,yyyy - hh:mm AM/PM). I found this code but it throws out runtime error 91 : object variable or With Block variable not set. at line

" If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Address = cell.Address "

Sub Highlight_Duplicate_Entry()
  Dim ws As Worksheet
  Dim cell As Range
  Dim myrng As Range
  Dim clr As Long
  Dim lastCell As Range

  Set ws = ThisWorkbook.Sheets("Sheet1")
  Set myrng = ws.Range("D1:e" & Range("D" & ws.Rows.Count).End(xlUp).Row)
  With myrng
  Set lastCell = .Cells(.Cells.Count)
  End With
  myrng.Interior.ColorIndex = xlNone
  clr = 3

  For Each cell In myrng
  If Application.WorksheetFunction.CountIf(myrng, cell) > 1 Then
  ' addresses will match for first instance of value in range
  If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Address = cell.Address Then
  ' set the color for this value (will be used throughout the range)
  cell.Interior.ColorIndex = clr
  clr = clr + 1
  Else
  ' if not the first instance, set color to match the first instance
  cell.Interior.ColorIndex = myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Interior.ColorIndex
  End If
  End If
  Next
End Sub

 

[Nebu]

Hi:

I guess the problem is with this part "what:=cell" what is the value of cell, try giving cell.value. It would be much easier if you would have uploaded a sample file to give you a solution.

Thanks

 

[Marc L]

Hi !

As Find method works with display of cells, sometimes Cell.Text is better …
​

 

[shrivallabha]

Object error 91 means following portion returns nothing.

myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell)

It could be written like (untested).

if not myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell) is nothing then
' Your previous code here
endif

 

[IKHAN]

Thanks Guys , Was able to resolve using cell.Text

 

[Marc L]

As the display is not same as value, Text property is the only way !

But as shrivallabha had mention, becare when the search value does not
exist : must see and follow the sample within Find VBA help !
​

 

[Marc L]

In fact there is another way : see that thread…
​