Format conditionnel automatique

[ascal44]

Bonjour je sollicite votre aide,
je voudrais exécuter la macro "formatconditionnel" automatiquement de façon à appliquer le format conditionnel sur les cellules de A à P si il y a une valeure dans une des ces cellules ( A à P).

Cela à partir de la ligne 11 jusqu'a la fin.

Sub formatconditionnel()
'
' formatconditionnel Macro

    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CELLULE(""row"")=LIGNE()"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 6
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ET($G2<>"""";MOD(LIGNE();2)=0)"
    With Selection.FormatConditions(2).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(2).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(2).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(2).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(2).Interior.ColorIndex = 15
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2<>"""""
    With Selection.FormatConditions(3).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(3).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(3).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(3).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(3).Interior.Pattern = xlNone
End Sub

 

[Robert]

Re : Format conditionnel automatique

Bonjour Ascal, bonjour le forum,

Pourquoi ne pas appliquer la mise en forme conditionnelle par avance à la plage A11😛65536 ? Mais sinon je te propose la macro événementielle Change ci-dessous à placer dans l'onglet concerné :

Private Sub Worksheet_Change(ByVal Target As Range)
'si le changement a lieu ailleurs que dans la plage A11:P65536, sort de la procédure
If Application.Intersect(Target, Range("A11:P65536")) Is Nothing Then Exit Sub
If Target.Value <> "" Then 'condition : si le contenu la cellule n'est pas effacer
    Target.Select 'sélectionne la cellule éditée
    Call formatconditionnel 'applique le format conditionnel à la cellule éditée
Else 'sinon
   Selection.FormatConditions.Delete 'supprime l'éventuel format conditionnel à la cellule effacée
End If 'fin de la condition
End Sub