Code macro à améliorer en fonction de la valeur de B7

Salut le forum

J'ai le code ci-dessous qui me permet de personnaliser les mouvements du curseur dans une plage de donnée.

Je souhaite solliciter de votre part des retouches. J'explique un peu:

dans B13, je saisis le nom et prenom de mon client.

Dans la cellule suivante (B14) je dois saisir le nom du conjoint du client s'il est marié et du sexe féminin.

Je souhaite donc savoir si possible après avoir renseigné B13, si dans B7 il y'a "Mr" ou "Mle", que le curseur saute la cellule suivante (B14) pour se positionner dans la cellule suivante (B15).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValSaisie
Dim P As Integer
    On Error GoTo fin
    If Not Intersect(Range("B11,B30,B10,B33,B34"), Target) Is Nothing Then
        Application.EnableEvents = False
        ValSaisie = Target
        Application.Undo
        P = InStr(Target, ValSaisie)
        If P > 0 Then
            Target = Left(Target, P - 1) & Mid(Target, P + Len(ValSaisie) + 1)
            If Right(Target, 1) = "+" Then
                Target = Left(Target, Len(Target) - 1)
            End If
        Else
            If Target = "" Then
                Target = ValSaisie
            Else
                Target = Target & "+" & ValSaisie
            End If
        End If
        Application.EnableEvents = True
    End If

    'GESTION CURSEUR SUR COMPTE ANCIEN CLIENT
    If Range("B4").Value = "ANCIEN CLIENT" Then
        If Target.Address = "$B$5" And Target.Value <> "" Then
            Range("B7").Select

        'AVEC COMPTE CHEQUE
        ElseIf InStr(1, Range("B10"), "COMPTE CHEQUE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$37" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$39" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                'Range("d3").Select
                Range("d3").Select
            Else: GoTo fin
            End If

        'AVEC COMPTE EPARGNE PRIVE
        ElseIf InStr(1, Range("B10"), "COMPTE EPARGNE PRIVE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$37" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$39" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B45").Select
            ElseIf Target.Address = "$B$45" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                Range("d3").Select
            Else: GoTo fin
            End If

        'AVEC COMPTE EPARGNE PUBLIQUE
        ElseIf InStr(1, Range("B10"), "COMPTE EPARGNE PUBLIQUE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$41" And Target.Value <> "" Then
                Range("B42").Select
                MsgBox ("Actualiser l'Ecran  avant de continuer svp!")
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                Range("d3").Select
            Else: GoTo fin
            End If
        End If

    'GESTION CURSEUR SUR COMPTE ANCIEN CLIENT PACK FONX
    ElseIf Range("B4").Value = "AC PACK FONX" Then
        If Target.Address = "$B$5" And Target.Value <> "" Then
            Range("B7").Select
            MsgBox ("FAIRE SIGNER L'ASSURANCE  AVANT DE CONTINUER SVP!")

        'AVEC COMPTE CHEQUE
        ElseIf InStr(1, Range("B10"), "COMPTE CHEQUE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$37" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$39" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("E35").Select
            ElseIf Target.Address = "$E$35" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
            Range("d3").Select
            Else: GoTo fin
            End If

        'AVEC COMPTE EPARGNE PUBLIQUE
        ElseIf InStr(1, Range("B10"), "COMPTE EPARGNE PUBLIQUE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$38" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$41" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("E35").Select
            ElseIf Target.Address = "$E$35" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                Range("d3").Select
            Else: GoTo fin
            End If
        End If

    'GESTION CURSEUR SUR COMPTE ANCIEN CLIENT PACK SAL
    ElseIf Range("B4").Value = "ANCIEN CLIENT PACK SAL" Then
        If Target.Address = "$B$5" And Target.Value <> "" Then
            Range("B7").Select
            MsgBox ("FAIRE SIGNER L'ASSURANCE AVANT DE CONTINUER SVP!")

        'AVEC COMPTE CHEQUE
        ElseIf InStr(1, Range("B10"), "COMPTE CHEQUE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$37" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$39" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                Range("d3").Select
            Else: GoTo fin
            End If

        'COMPTE EPARGNE PRIVE
        ElseIf InStr(1, Range("B10"), "COMPTE EPARGNE PRIVE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$37" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$39" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B45").Select
            ElseIf Target.Address = "$B$45" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                Range("d3").Select
            Else: GoTo fin
            End If

        'AVEC COMPTE EPARGNE PUBLIQUE
        ElseIf InStr(1, Range("B10"), "COMPTE EPARGNE PUBLIQUE") > 0 Then
            If Target.Address = "$B$16" And Target.Value <> "" Then
                Range("B18").Select
            ElseIf Target.Address = "$B$26" And Target.Value <> "" Then
                Range("B28").Select
            ElseIf Target.Address = "$B$29" And Target.Value <> "" Then
                Range("B31").Select
            ElseIf Target.Address = "$B$32" And Target.Value <> "" Then
                Range("B37").Select
            ElseIf Target.Address = "$B$38" And Target.Value <> "" Then
                Range("B39").Select
            ElseIf Target.Address = "$B$41" And Target.Value <> "" Then
                Range("B42").Select
            ElseIf Target.Address = "$B$42" And Target.Value <> "" Then
                Range("B43").Select
            ElseIf Target.Address = "$B$43" And Target.Value <> "" Then
                Range("B46").Select
            ElseIf Target.Address = "$B$46" And Target.Value <> "" Then
                Range("B47").Select
            ElseIf Target.Address = "$B$47" And Target.Value <> "" Then
                Range("B48").Select
            ElseIf Target.Address = "$B$48" And Target.Value <> "" Then
                Range("B49").Select
                Range("d3").Select
            Else: GoTo fin
            End If
        End If
    End If
fin:
    Application.EnableEvents = True   ' Dans tous les cas on remet les évènements en service
End Sub 

Cordialement et merci pour vos contribution.

Salut,

Essaie de placer ce code quelque part dans ta macro, par exemple tout à la fin, juste avant la ligne Application.EnableEvents = True ' Dans tous les cas on remet les évènements en service

    If Not Application.Intersect(Target, Range("B13")) Is Nothing Then
        If Range("B7") = "Mr" Or Range("B7") = "Mle" Then
            Range("B15").Activate
        Else
            Range("B14").Activate
        End If
    End If

A te relire.

C'est super Yvouille.

Merci pour ton génie.

Je profite également souhaiter à tous les membres du forum une bonne fête de l'ascension et la bénédiction de ce forum.

Merci infiniment à tous et plus particulièrement Yvouille.