custom data validation in a VBA form

As Comintern suggested, use Find() method of Range object, with code like:

Set f = rngToSerachIn.Find(what:=factureNo, LookIn:=xlValues, lookat:=xlWhole)

where

• f is a range variable where to store the range with the searched value

• rngToSerachIn is the range where to search the value

• factureNo is the value to search for

furthermore it seems to me your invoices will be stored in rows from 12 downwards, so it could be useful to write a generic function to get first empty cell in a given column of a given worksheet ranging from a certain row

Since it'd be a good practice to demand specific tasks to Sub/Function to improve both code readability and maintenance, you could do that for:

• getting first empty row after last non empty one starting from a given row in a given column of a given worksheet

• validating invoice number

• filling worksheet ranges

• formatting invoice cell

as follows:

Option Explicit

Private Sub CommandButton1_Click()
    Dim L As Long
    Dim factureWs As Worksheet

    If MsgBox("Confirm?", vbYesNo, "Confirming new invoice") = vbNo Then Exit Sub

    Set factureWs = Worksheets("FACTURE") '<--| set the worksheet you want to work with

    L = GetLastNonEmptyRow(factureWs, "D", 12) + 1 '<--| get passed worksheet first empty row after last non empty one in column "D" from row 12 (included)

    If L > 12 Then If Not CheckDuplicate(Me.TextBox2, factureWs.Range("D12:D" & L - 1)) Then Exit Sub '<--| exit if duplicated non accepted by the user

    FillRanges factureWs, L '<--| fill worksheet ranges with userfom controls values

    FormatInvoice factureWs.Range("B" & L) '<--| color invoice cell depending on option buttons values
End Sub

Function GetLastNonEmptyRow(ws As Worksheet, colIndex As String, firstRow As Long) As Long
    Dim lastRow As Long
    With ws
        lastRow = .Cells(.Rows.Count, colIndex).End(xlUp).row ' <--| get last non empty row in given column
        If lastRow = 1 Then If IsEmpty(.Range(colIndex & 1)) Then lastRow = 0  '<--| handle the case of an empty column
        If lastRow < firstRow Then lastRow = firstRow - 1 '<--| handle the case the last non empty row is above the first passed one
    End With
    GetLastNonEmptyRow = lastRow
End Function

Function CheckDuplicate(factureNo As String, rng As Range) As Boolean
    Dim f As Range
    Set f = rng.Find(what:=factureNo, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        CheckDuplicate = MsgBox("This invoice number already exist!" & vbCrLf & vbCrLf & "Continue?", vbExclamation + vbYesNo, "Duplicate alert") = vbYes
    Else
        CheckDuplicate = True
    End If
End Function

Sub FormatInvoice(rng As Range)
    Dim thColor As XlThemeColor

    With Me
        Select Case True
            Case .OptionButton1
                thColor = xlThemeColorAccent3
            Case .OptionButton2
                thColor = xlThemeColorAccent1
            Case .OptionButton3
                thColor = xlThemeColorAccent4
            Case Else
                thColor = xlThemeColorAccent2
        End Select
    End With
    FormatCell rng, thColor
End Sub

Sub FillRanges(ws As Worksheet, L As Long)
    With ws
        .Range("C" & L).Value = (Now)
        .Range("D" & L).Value = Me.TextBox2
        .Range("E" & L).Value = Me.TextBox3
        .Range("F" & L).Value = Me.TextBox4
        .Range("G" & L).Value = Me.TextBox5
        .Range("K" & L).Value = Me.ComboBox1
        .Range("L" & L).Value = Me.ComboBox2
        .Range("M" & L).Value = Me.ComboBox3
        .Range("N" & L).Value = Me.TextBox9
        .Range("O" & L).Value = Me.TextBox10
        .Range("R" & L).Value = Me.TextBox39
        .Range("P" & L).Value = Me.TextBox40
    End With
End Sub

you may find it useful and follow this pattern in your subsequent coding