Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour cells with dupilate numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour cells with dupilate numbers
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 -- "Simon" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour cells with dupilate numbers
"Don Lloyd" wrote in message ...
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colour numbers | Excel Worksheet Functions | |||
colour cells, same numbers different worksheets | Excel Worksheet Functions | |||
Changing all cells in one colour to a different colour | Excel Discussion (Misc queries) | |||
Help Please.....numbers reaching limits and cells changing colour | Excel Worksheet Functions | |||
Adding numbers in cells based on colour | Excel Worksheet Functions |