(VBA) Search duplicates in one columns, determine it by another

[sh1ne]

Hi Guys,

I want to describe my problem as much as I can. My target is to create macro which will highlight the duplicated rows searching in column B, but all is determinated by Column A. Values in column B can be same and not highlighted if values in column A are different. CountIf is good for highlight cells within range for only duplicates, what about conditional highlight? InStr would be good, but how I can store informations about which cells are need to be compared(there can be more than 1000 rows)

To make it clear, below is example:

colA____colB
ABC____XYZ
ABC____YZX
BCA____XYZ
BCA____ZXY
CBA____XYZ
ABC____XYZ

As you see, there are many XYZ in column B, but everything is ok if colA is different. Any ideas how macro should look like?

 

[sh1ne]

I have no experience with Mac - see if this works

Sub HihglightDupes()
    Dim lastRow As Long, rCell As Range
    Dim lCounter As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each rCell In Range("A2:A" & lastRow)
        lCounter = _
            Evaluate(Replace("=COUNTIFS(A2:A@,""" & rCell.Value & """,B2:B@,""" & rCell.Offset(, 1) & """)", "@", lastRow))
        If lCounter > 1 Then rCell.Resize(, 2).Font.Color = vbRed
    Next rCell
End Sub

M.

Thanks a lot mate, with little changes it's working properly like I wanted.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.