Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim col As Variant
Cells.Validation.Delete 'RAZ des validations
col = ActiveCell.Column
If ActiveCell.Row = 1 Or Not (col = 2 Or col = 3 Or col = 6 Or col = 9) Then Exit Sub
With Feuil2 'CodeName
If col <> 3 Then
col = IIf(col = 2, 1, 10)
Else
col = Application.Match(ActiveCell(1, 0), .Rows(2), 0)
End If
If IsNumeric(col) Then ActiveCell.Validation.Add xlValidateList, Formula1:="=" & .Range(.Cells(3, col), .Cells(.Rows.Count, col).End(xlUp)).Address(External:=True)
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, i As Byte
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
'---effacements des colonnes C et D---
Set r = Intersect(Target, Range("B2:B" & Rows.Count))
If Not r Is Nothing Then Intersect(r.EntireRow, [C:D]) = ""
'---calcul de la colonne D---
Set r = Intersect(Target, Range("C2:D" & Rows.Count), UsedRange)
If Not r Is Nothing Then
Set r = Intersect(r.EntireRow, [D:D])
For Each r In r.Areas
r = "=IFERROR(VLOOKUP(RC[-1],'Listes déroulantes'!R2C7:R7C8,2,0),"""")"
r = r.Value 'supprime les formules
Next r
End If
Application.EnableEvents = True 'réactive les évènements
'---Bordures diagonales---
Set r = Intersect(Target, Range("G2:G" & Rows.Count), UsedRange)
If Not r Is Nothing Then
For Each r In r
For i = 5 To 6
With Intersect(r.EntireRow, Range("I:K")).Borders(i)
If r = 100 Then .Weight = xlThin Else .LineStyle = xlNone
End With
Next i, r
End If
End Sub