Auto highlight duplicates

[Ignition1]

Hi all

Is there a way (well...there is always a way) for Excel to automatically highlight duplicates once something has been typed.

I.e. In Column A is a list of names.

Dave
John
Sara

As I enter new names, if I put Dave again then that cell is highlighted in yellow. Or even better if the name matches a few phrases in a cell previous to it - then highlight in yellow.

I'm learning VBA but I know the basics - this feels like a task for VBA.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    If WorksheetFunction.CountIf(Columns("A"), Target.Value) > 1 Then Target.Interior.ColorIndex = 6
End If
End Sub

 

[T. Valko]

Hi all

Is there a way (well...there is always a way) for Excel to automatically highlight duplicates once something has been typed.

I.e. In Column A is a list of names.

Dave
John
Sara

As I enter new names, if I put Dave again then that cell is highlighted in yellow. Or even better if the name matches a few phrases in a cell previous to it - then highlight in yellow.

I'm learning VBA but I know the basics - this feels like a task for VBA.

You can use conditional formatting to do this.

Want to do it that way?

 

[Ignition1]

Perfect! Thanks VoG - I need to study that code and figure out how/why that worked.

Conditional Formatting would have also done the trick (though I wasn't sure if it ran automatically as I added new names or if I had to run formatting again).

 

[Dannyh1]

In Conditional Formatting try:

=IF(MATCH(A4,$A$1:A3,0)>0,1,0)


*beaten to it