Microsoft 365 comparaison texte

svn

XLDnaute Nouveau
bonjour à tous ,

vous j'ai besoin d'aide pour optimiser mon code svp, comme vous pouvez le constaster , je ne suis pas un expert en vba
j'aimerais écrire ce code avec des boucles pour le rendre plus courts svp merci.

j'ai vraiment besion de votre aide

Sub formeconditionnelle()
'
'cette macro me permet de mettre en couleurs les cellules en fonction des conditions imposées, la macro permettre de comparer le texte dans les cellules

'1a) recherche des valeurs differents dans la feuille ordo par rapport à GM

Dernligne11 = Sheets("Ordo").Range("A" & Rows.Count).End(xlUp).Row
Dernligne22 = Sheets("GM").Range("A" & Rows.Count).End(xlUp).Row


ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

Sheets("Ordo").Select
Range("E2:I" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$C:$C;$C2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


'2a) Pour verifier les valeurs qui n'existent pas dans le Ordo

Range("C2:I" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$C:$C;$C2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


'1b) recherche des valeurs differents dans la feuille GM par rapport à ordo
Sheets("GM").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E2:I" & Dernligne22).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$C:$C;$C2)"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'2b) Pour verifier les valeurs qui n'existe pas dans la feuille GM

Range("C2:I" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$C:$C;$C2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


'3a) Pour vérifier que les valeurs existent dans les deux tableaux

Sheets("Ordo").Select

Range("E2:E" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$M:$M;$M2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("F2:F" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$O:$O;$O2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("G2:G" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$Q:$Q;$Q2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("H2:H" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$S:$S;$S2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("I2:I" & Dernligne11).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(GM!$U:$U;$U2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'3b) Pour vérifier que les valeurs existent dans les deux tableaux

Sheets("GM").Select

Range("E2:E" & Dernligne22).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$M:$M;$M2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("F2:F" & Dernligne22).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$O:$O;$O2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("G2:G" & Dernligne22).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$Q:$Q;$Q2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("H2:H" & Dernligne22).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$S:$S;$S2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("I2:I" & Dernligne22).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NB.SI(Ordo!$U:$U;$U2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub
 

Statistiques des forums

Discussions
311 725
Messages
2 081 943
Membres
101 849
dernier inscrit
florentMIG