L'exécution de la fonction est très lente

bonjour , j'appel a votre aide pour un problème sur Excel ,l'exécution de la fonction est très lente , voila le code et Merci d'avance :

Sub Mise1()

Dim O As Worksheet

Dim pf As Range 'déclare la variable pf

Dim pr As Range 'déclare la variable pr

Dim cf As Range 'déclare la variable cf

Dim cr As Range 'déclare la variable cr

For Each O In Sheets

Select Case O.Name

Case "Effectifs Flandres Artois", "RTT EMPLOYEUR", "S10", "S09", "S08", "S07", "S06", "S05", "S04", "S03", "S02", "S01", "S52", "S53", "S52", "S51", "S50", "S49", "S48

Case Else

With O

Set pf = .Range("E192:E246")

Set pr = .Range("D6:F180")

For Each cf In pf

For Each cr In pr

If cr.Value = cf.Value Then

cf.Font.ColorIndex = 2

Exit For

Else: cf.Font.ColorIndex = 1

End If

Next cr

Next cf

End With

End Select

Next O

End Sub

Bonjour,

Essaie ainsi :

Sub Mise1()
'Déclaration des variables
Dim O As Worksheet
Dim pf As Range
Dim pr As Range
Dim cf As Range
Dim cr As Range
Dim modeCalc As XlCalculation

    With Application
        modeCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    For Each O In ActiveWorkbook.Worksheets
        Select Case O.Name
            Case "Effectifs Flandres Artois", "RTT EMPLOYEUR", "S10", "S09", "S08", "S07", "S06", _
                 "S05", "S04", "S03", "S02", "S01", "S52", "S53", "S52", "S51", "S50", "S49", "S48"
            Case Else
                With O
                    Set pf = .Range("E192:E246")
                    Set pr = .Range("D6:F180")
                    For Each cf In pf
                        For Each cr In pr
                            If cr.Value = cf.Value Then
                                cf.Font.ColorIndex = vbWhite    '2
                                Exit For
                            Else: cf.Font.ColorIndex = vbBlack  '1
                            End If
                        Next cr
                    Next cf
                End With
        End Select
    Next O

    Application.Calculation = modeCalc

End Sub

Merci Jean-Eric , ca marche bien

j'ai une question par rapport au liaison entre la macro et une zone de liste , est ce que je peux intégrer sur le code par exemple :

Else: cf.Font.ColorIndex = vbBlack '1 & (copier et coller cette case sur une zone de liste )

merci d'avance