Highlight duplicates if col A and B are the same but not col C

**Hond70** · 10-11-2011, 05:58 PM

I have 3 columns:

colA: city
colB: street
colC: name

1 Amsterdam Rokin Jos
2 Amsterdam Beursplein Jos
3 Amsterdam Rokin Jos
4 Amsterdam Rokin Erik
5 Rotterdam Boompjes Erik

What I want is that a formula goes through every row and highlights every city where colA and colB are the same AND where colC is different. So in this example the formula should NOT highlight row 3, but only row 1 (the first with the name Jos) and row 4.

I have found a very neat formula from Techonthenet (http://www.techonthenet.com/excel/macros/test_dups2.php). But this one only looks at colA and colB. Any help is appreciated. I run Excel 2010, but if it works on Excel 2003, it will be better.

**tigeravatar** · 10-11-2011, 06:33 PM

Hond70,

The link you provided shows a macro solution, so I'm hoping you're comfortable using macros. You can use the following to accomplish what you're looking for:

Sub btn_HighlightDuplicates_Click()
    
    Dim rngUnqCty As Range: Set rngUnqCty = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
    Dim rngUnqStr As Range: Set rngUnqStr = rngUnqCty.Offset(0, 1)
    
    Dim CtyCell As Range, StrCell As Range
    Dim rngVis As Range, VisCell As Range
    Dim strName As String: strName = vbNullString
    
    Application.ScreenUpdating = False
    Intersect(ActiveSheet.UsedRange, Columns("A")).AdvancedFilter xlFilterCopy, , rngUnqCty, True
    Intersect(ActiveSheet.UsedRange, Columns("B")).AdvancedFilter xlFilterCopy, , rngUnqStr, True
    Set rngUnqCty = Range(rngUnqCty.Offset(1), rngUnqCty.End(xlDown))
    Set rngUnqStr = Range(rngUnqStr.Offset(1), rngUnqStr.End(xlDown))
    
    With Intersect(ActiveSheet.UsedRange, Columns("A:C"))
        .Interior.ColorIndex = 0
        For Each CtyCell In rngUnqCty
            For Each StrCell In rngUnqStr
                .AutoFilter 1, CtyCell.Value
                .AutoFilter 2, StrCell.Value
                On Error Resume Next: Set rngVis = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                If Not rngVis Is Nothing Then
                    If rngVis.Cells.Count > 1 Then
                        For Each VisCell In rngVis
                            If Cells(VisCell.Row, "C").Value <> strName Then
                                strName = Cells(VisCell.Row, "C").Value
                                VisCell.Resize(1, 3).Interior.ColorIndex = 3
                            End If
                        Next VisCell
                    End If
                    Set rngVis = Nothing
                    strName = vbNullString
                End If
            Next StrCell
        Next CtyCell
        .AutoFilter
    End With
    
    Union(rngUnqCty, rngUnqStr).EntireColumn.Delete
    Application.ScreenUpdating = True
    
End Sub