Sub SetSpecialFormatCondition()
    Dim WS As Worksheet
    Dim RangeOfCells As Range
    Dim S As String, FormulaStrRed As String, FormulaStrGreen As String
    Dim FC As FormatCondition


    For Each WS In ThisWorkbook.Worksheets
        S = Trim(WS.Range("AU1").Value)
        If S = "Etat" Then
            FormulaStrRed = "=$AU2=" & """" & "clôturé" & """"    '=$AU2="clôturé"
            FormulaStrGreen = "=$AU2=" & """" & "en cours" & """"    '=$AU2="en cours"


            Set RangeOfCells = WS.Range("A2:BH" & WS.Range("AU" & WS.Rows.Count).End(xlUp).Row)
            With RangeOfCells
                For Each FC In .FormatConditions
                    If FC.Type = xlExpression And (FC.Formula1 = FormulaStrRed Or FC.Formula1 = FormulaStrGreen) Then
                        FC.Delete
                    End If
                Next FC
                
                Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:=FormulaStrGreen)
                With FC
                    .SetFirstPriority
                    With .Font
                        .ThemeColor = xlThemeColorDark1
                        .ThemeFont = xlThemeFontNone
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 5287936
                    End With
                    .StopIfTrue = True
                End With
                
                Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:=FormulaStrRed)
                With FC
                    .SetFirstPriority
                    With .Font
                        .ThemeColor = xlThemeColorDark1
                        .ThemeFont = xlThemeFontNone
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                    End With
                    .StopIfTrue = True
                End With
            End With
        End If
    Next WS
End Sub