Colour cells with dupilate numbers

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Is it possible to have a column of numbers and as I enter
new numbers to the column the cell changes colour if the
number already appears in the column.

Column

100052
100053
100098
100032


New number entered

100056 - No colour change

100053 - Cell colour changed


Many Thanks

Simon
 
The folowing may get you going.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False

Dim Rw, Col, X
Rw = Target.Row: Col = Target.Column
For X = 1 To Rw - 1
If Cells(X, 1) = Target Then
Cells(Rw, 1).Font.ColorIndex = 3
End If
Next

Application.EnableEvents = True
End Sub

Assuming that the data is all in column 1 (A), duplicated entries will be
coloured RED.

regards,

don
 
Don Lloyd said:
The folowing may get you going.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False

Dim Rw, Col, X
Rw = Target.Row: Col = Target.Column
For X = 1 To Rw - 1
If Cells(X, 1) = Target Then
Cells(Rw, 1).Font.ColorIndex = 3
End If
Next

Application.EnableEvents = True
End Sub

Assuming that the data is all in column 1 (A), duplicated entries will be
coloured RED.
Click to expand...

As a variation (which is proably far from foolproof!):
Put this declaration at the top of the worksheet code module
Dim NumberList As New Collection

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
On Error Resume Next
With Target
NumberList.Add .Text, .Text
'error generated if number already exists
If Err.Number <> 0 Then
.Interior.ColorIndex = 3
End If
End With
On Error GoTo 0
Application.EnableEvents = True
End Sub

This will work where ever you put the numbers on the sheet
regards
Paul
 
Back
Top