jipi06
XLDnaute Junior
Bonjour à toutes et tous
j'ai écris ce code qui me permet sur le changement d'une cellule d'affecter la même valeur à 2 autres cellules.
J'aimerais savoir si il est possible de le simplifier et concaténer 2 ou N affectations de type :
If Cell.Value = "" Then Cell.Offset(0, -2) = "" AND Cell.Offset(0, 2) = ""
Merci beaucoup de votre aide
Jipi
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "NN" Then Cell.Offset(0, 2) = "NN"
Next Cell
End If
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "NN" Then Cell.Offset(0, -2) = "NN"
Next Cell
End If
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "" Then Cell.Offset(0, -2) = ""
Next Cell
End If
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "" Then Cell.Offset(0, 2) = ""
Next Cell
End If
Application.EnableEvents = True
End Sub
j'ai écris ce code qui me permet sur le changement d'une cellule d'affecter la même valeur à 2 autres cellules.
J'aimerais savoir si il est possible de le simplifier et concaténer 2 ou N affectations de type :
If Cell.Value = "" Then Cell.Offset(0, -2) = "" AND Cell.Offset(0, 2) = ""
Merci beaucoup de votre aide
Jipi
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "NN" Then Cell.Offset(0, 2) = "NN"
Next Cell
End If
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "NN" Then Cell.Offset(0, -2) = "NN"
Next Cell
End If
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "" Then Cell.Offset(0, -2) = ""
Next Cell
End If
If Not Intersect(Target, Range("c1:c20")) Is Nothing Then
Application.EnableEvents = True
For Each Cell In Target
If Cell.Value = "" Then Cell.Offset(0, 2) = ""
Next Cell
End If
Application.EnableEvents = True
End Sub