Highlight duplicate rows in two columns

[Julyna]

Hi, I'm struggling to find and highlight more than two duplicates in a rows, based on two columns, i.e I have a set number in col Y and a system defect in col AE and I have to highlight the same two data down across the rows. I actually can't exclude the columns between Y and AE, so it's highlighting all between them Can you advice, please?
That's my code below:

Dim lngMyCol As Long, _
lngEndRow As Long
Dim strMyCol As String
Dim lngStartRow As Long
Dim rngCell As Range

Application.ScreenUpdating = False

lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lngEndRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)

lngStartRow = 3 'Starting row number. Change to suit.

With Columns(strMyCol)
With Range(strMyCol & lngStartRow & ":" & strMyCol & lngEndRow)
.Formula = "=Y2&AE2"
.Value = .Value
End With
End With

For Each rngCell In Range(strMyCol & lngStartRow & ":" & strMyCol & lngEndRow)
If Evaluate("Countif(" & strMyCol & lngStartRow & ":" & strMyCol & lngEndRow & "," & rngCell.Address & ")") > 2 Then
Range("Y" & rngCell.Row & ":AE" & rngCell.Row).Interior.Color = RGB(0, 255, 0) 'Highlights dups in green. Change to suit.
End If
Next rngCell

Columns(strMyCol).Delete 'Delete helper column containing primary key

Application.ScreenUpdating = True

MsgBox "Duplicate rows have now been highlighted"

End Sub

Thank you in advance!

 

[Fluff]

Hi & welcome to MrExcel
Try this mod

Range("Y" & rngCell.Row & "[COLOR=#ff0000],[/COLOR]AE" & rngCell.Row).Interior.Color = RGB(0, 255, 0) 'Highlights dups in green. Change to suit.

 

[Julyna]

Hi, Fluff,
Thanks a lot! I tried before with this and it didn't work, but I'm not a coder, so probably I had messed it up Now works, perfect!!! I appreciate it!
Would you help me with something else, please? In column D I have the dates of the defects and I want to highlight the column Y and AE (as in the macro above) only in the last 90 days period from today. The conditional formatting didn't work either.

 

[Fluff]

How about

For Each rngCell In Range(strMyCol & lngStartRow & ":" & strMyCol & lngEndRow)
   If Evaluate("Countif(" & strMyCol & lngStartRow & ":" & strMyCol & lngEndRow & "," & rngCell.Address & ")") > 2 Then
      If Range("D" & rngCell.Row) > Date - 90 Then Range("Y" & rngCell.Row & ",AE" & rngCell.Row).Interior.Color = RGB(0, 255, 0)      'Highlights dups in green. Change to suit.
   End If
Next rngCell

 

[Julyna]

Hi, Fluff, thank you, it works generally, but it's not very precise, sometimes highlighting only one defect or missing duplicated ones in another case. I hope I'll find out why. Cheers!