Re : Comparaison listes
bonjour,
j'utilise le code ci dessous pour ce genre de comparaison, à partir d'une sélection d'une plage de cellules (dans deux colonnes seulement, les deux à comparer, vous pouvez coller B et F par exemple pour les comparer)
si ça peut aider...
Application.ScreenUpdating = False
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("G3").Select
ActiveCell.FormulaR1C1 = "=""liste: ""&(R[-2]C[-6])"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=""liste: ""&(R[-3]C[-5])"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-1]C[-7]:R[65533]C[-7])"
Range("H4").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-2]C[-6]:R[65532]C[-6])"
Range("I3").Select
ActiveCell.FormulaR1C1 = "valeurs"
Range("I4").Select
ActiveCell.FormulaR1C1 = "valeurs"
Range("I5").Select
aaa2 = Cells(3, 8)
bbb2 = Cells(4, 8)
'
Range("C1").Select
ActiveCell.FormulaR1C1 = "colonne A présent dans colonne B"
Range("D1").Select
ActiveCell.FormulaR1C1 = "colonne B présent dans colonne A"
Range("C1😀1").Select
With Selection
.WrapText = True
End With
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-2])"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-2])"
Range("C2").Select
Selection.Copy
Range(Cells(2, 3), Cells(aaa2 + 1, 3)).Select
'Range("C2:C30").Select
ActiveSheet.Paste
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(2, 4), Cells(bbb2 + 1, 4)).Select
ActiveSheet.Paste
Range("G3").Select
Application.CutCopyMode = False
'
Range("J3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-6]:R[64997]C[-6])"
Range("J4").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C[-7]:R[64996]C[-7])"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=""sont présentes dans ""&R[1]C[-4]"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=""sont présentes dans ""&R[-1]C[-4]"
Range("K5").Select
'coloration
Range("A:A,C:C").Select
Range("C1").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("G3:M3").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("B:B,D😀").Select
Range("D1").Activate
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("G4:M4").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("G8").Select
ActiveWindow.SplitRow = 3.94117647058824
ActiveWindow.FreezePanes = True
'
Range("G3:G4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J3:J4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J3:M4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Columns("G:G").ColumnWidth = 16.43
Range("G3:I4").Select
Range("I3").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G8").Select
'MEFC rouge si 0
Range(Cells(2, 3), Cells(aaa2 + 1, 3)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Interior.ColorIndex = 46
Range("H15").Select
Range(Cells(2, 4), Cells(bbb2 + 1, 4)).Select
'
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Interior.ColorIndex = 46
Range("H15").Select