Brent

asked on

# 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

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

Do you enter the sequences manually?

ASKER

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
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Errors"
With Sheets("Errors")
.Range("A1").Resize(, 3) = Array("Sheet", "Not found", "Duplicate")
.Range("A2").Resize(n, 3) = vOut
End With
End Sub
```

ASKER

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

ASKER CERTIFIED SOLUTION

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.

ASKER

Yes, that will work fine.

Thanks for the offer to explain, but seems it works just fine.

Thanks

Thanks for the offer to explain, but seems it works just fine.

Thanks