functions or vba solution
 

Nothing came out from Functions maybe here?

Sheet1 - logged ticket books to sales persons

name beginno endno
a 51 100
b 101 150


I do below checks by vba if user makes a wrong entry say


d 75 - msgbox " Number already in use "
e 151 141 msgbox " Wrong entry"


But couldn't do below check


f 1 200 msgbox " Numbers already in
use"


Any help ? Thank you

functions or vba solution
 

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range
Dim iCt as Long
If Target.Column = 2 And Target < "" Then
Set rng = Range("B2:B" & Target.Row - 1)
For Each c In rng
If Target = c And Target <= c.Offset(0, 1) Then
MsgBox "Error. Number already in use."
Target = ""
End If
Next c
End If
If Target.Column = 3 And Target < "" Then
If Target < Target.Offset(0, -1) Then
MsgBox "Error. End number must be = begin number."
Target = ""
Exit Sub
End If
Set rng = Range("B2:B" & Target.Row - 1)
For iCt = Target.Offset(0, -1) + 1 To Target
For Each c In rng
If Target = c And Target <= c.Offset(0, 1) Then
MsgBox "Error. Number(s) already in use."
Target = ""
End If
Next c
Next iCt
End If
End Sub

Hth,
Merjet

functions or vba solution
 

Thank you very much Merjet.
One more thing though:
How can I avoid flying over numbers ?

Like 1 to 200 ?

functions or vba solution
 

How can I avoid flying over numbers ?
Like 1 to 200 ?

Oops.
Change: If Target = c And Target <= c.Offset(0, 1)
To: If iCt = c And iCt <= c.Offset(0, 1)

Merjet

functions or vba solution
 

Exactly what I needed ! Thank you vey much.
Kind regards