# Conditional formatting for duplicates and all numbers in a table

I have a very simple, but important task. I have attached a sheet and in that sheet I have a list of numbers in order. These numbers are station numbers that are used in a sequence to determine which station are dispatched to runs. I have about 200 sheets that I have to enter these station run numbers in various sequences.

It is very very important that each of the numbers shows up in each sheet. It is equal as important that I do no duplicate any numbers while enter them. I will make a validation list, so only these numbers will be accepted.

My questions is can I do some type of conditional formatting or validation so I don't repeat any numbers or I don't forget any numbers in each sheet?

Thanks for any help.

Brent RUN-ORDER.xls
StephenJR

Do you enter the sequences manually?

Yes, I enter the sequence manually.
Don't know if this is feasible for your actual file, but will produce a sheet at the end listing errors.
``````Sub x()

Dim ws As Worksheet, v, i As Long, vOut(), n As Long

v = Array(3, 31, 33, 1, 30, 34, 51, 5, 24, 61, 21, 22, 41, _
53, 23, 52, 60, 42, 65, 43, 63, 56, 44, 77, 45, _
"B1", "B3", "B2", "B5", "B6", "B4")

ReDim Preserve vOut(1 To Worksheets.Count * UBound(v), 1 To 3)

For Each ws In Worksheets
For i = LBound(v) To UBound(v)
If ws.UsedRange.Find(What:=v(i), Lookat:=xlWhole) Is Nothing Then
n = n + 1
vOut(n, 1) = ws.Name
vOut(n, 2) = v(i)
End If
If WorksheetFunction.CountIf(ws.UsedRange, v(i)) > 1 Then
n = n + 1
vOut(n, 1) = ws.Name
vOut(n, 3) = v(i)
End If
Next i
Next ws

With Sheets("Errors")
.Range("A2").Resize(n, 3) = vOut
End With

End Sub``````

I'll be honest, this solution is a little difficult for me to understand.
dlmille

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Happy to try to explain if you think it's an approach you want to pursue, there will be others.