Find duplicate combination in list and highlight cell

[sadonnie]

I have the following table as an example. Here is what I am trying to accomplish using Worksheet_Change(ByVal Target As Range) <---I assume this is the most logical approach to invoking the logic

This is all in 1 sheet (say Sheet1). I have a list of Applications in Col A (the same application can be listed many times in this column). Col B can be ignored. Columns C, D, and E will either be left blank or have a value of 1.

Using the Supply Chain example, when I get to row 4 and I put a value of 1 in Col E, it should invoke Worksheet_Change(ByVal Target As Range) and I am trying to get the code to identify that the Application Name + Col E value is already equal to a value of 1 (combo exists in Row 2 already). Therefore it shades the cell in E4 and displays a note to the user since this is a duplication scenario.

Same would apply if user entered a value of 1 in Col C or D and the logic validates that the App Name + Col C or D combination already exists in the sheet.

Any suggestions or recommendations is greatly appreciated.

 

[bebo021999]

Try:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, count&, cell As Range
With Target
    lr = Cells(Rows.count, "A").End(xlUp).Row
    count = WorksheetFunction.CountIfs(Range("A2:A" & lr), Cells(.Row, "A"), _
    Range(Cells(2, .Column), Cells(lr, .Column)), Target)
End With
If Intersect(Target, Range("C2:E" & lr)) Is Nothing Then Exit Sub
    For Each cell In Range("A2:A" & lr)
        If cell.Value = Cells(Target.Row, "A").Value Then
            With Cells(cell.Row, Target.Column)
                .ClearComments
                .Interior.Color = xlNone
                If count > 1 Then
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:="Person already assigned, pls remove"
                    .Interior.ColorIndex = 46
                End If
            End With
        End If
    Next
End Sub

 

[sadonnie]

Thank you for your assistance. This works perfectly and much more simplified code then what I had initially developed.
Much appreciated for your support!