Dim x
x = Application.CountIf(Range("b2"), ">1")
Range("a5") = IIf(x, "doublon", "Début")
'Code à inserer dans un Module Standard
Sub test()
Dim plage As Range, cel As Range, x
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Feuil1") 'Nom de la feuille à modifier
Set plage = .Range("a5:a5005")
plage.ClearContents
x = Application.CountIf(.Range("b2"), ">1")
For Each cel In plage
cel.Value = IIf(x, "doublon", "Début")
Next cel
End With
Application.EnableEvents = True
End Sub
'---------------------------------------------------------
'Dans le module de la feuille
Private Sub Worksheet_Change(ByVal Target As Range)
Dim plage As Range, cel As Range, x
Application.ScreenUpdating = False
Application.EnableEvents = False
Set plage = Range("a5:a5005")
plage.ClearContents
x = Application.CountIf(Range("b2"), ">1")
For Each cel In plage
cel.Value = IIf(x, "doublon", "Début")
Next cel
Application.EnableEvents = True
End Sub
Sub f()
Range("A5:A" & [B65000].End(xlUp).Row).FormulaLocal = "=SI(NB.SI(B$2:B2;B2)>1;""doublon"";""Début"")"
End Sub
Sub Macro2()
derlig = Sheets("Feuil1").Range("B" & Rows.Count).End(xlUp).Rows + 1
Range("A5:A" & derlig).FormulaR1C1 = _
"=IF(COUNTIF(R2C[1]:RC[1],RC[1])>1,""doublon"",""Début"")"
End Sub
Sub Macro()
Range("A5").Formula = "=if(CountIf(B$2:B2,B2)>1,""doublon"",""début"")"
Range("A5").AutoFill Destination:=Range("A5:A" & Range("B" & Rows.Count).End(xlUp).Row + 3)
End Sub
Sub Compte_Doublons()
Dim lig, i As Integer, reponse as Boolean
With Feuil1
lig = .Range("b65000").End(xlUp).Row
For i = 2 To lig
reponse = Application.CountIf(.Range("b2:b65536"), Cells(i, 2)) > 1 'VRAI OU FAUX
.Cells(i, 1) = IIf(reponse, "Doublon", "Unique")
If reponse Then .Cells(i, 1).Font.ColorIndex = 3: .Cells(i, 1).Font.Bold = True: _
.Cells(i, 2).Font.ColorIndex = 3: .Cells(i, 2).Font.Bold = True:
Next i
End With
End Sub
Sub App_Defaults()
With Feuil1
.Range("a2:a65536").ClearContents
.Range("a2:b65536").Font.Bold = False
.Range("a2:b65536").Font.ColorIndex = 0
End With
End Sub