Y aurait-il une procedure plus "elegante" ou plus simple?
5 réponses
Steve
Bonjours,
Grace à vos conseils j'ai écris ce code qui fonctionne.
Je me demande si j'aurai pu mieux faire.
Peut être une boucle.....
Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Address = "$E$4" Then
Range("E13").Interior.ColorIndex = 3
End If
If Target.Address = "$E$13" Then
Range("E13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$4" Then
Range("i13").Interior.ColorIndex = 3
Range("i21").Interior.ColorIndex = 3
End If
If Target.Address = "$I$13" Then
Range("i13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$21" Then
Range("i21").Interior.ColorIndex = 36
End If
If Target.Address = "$M$4" Then
Range("M13").Interior.ColorIndex = 3
Range("M16").Interior.ColorIndex = 3
Range("M17").Interior.ColorIndex = 3
Range("M21").Interior.ColorIndex = 3
End If
If Target.Address = "$M$13" Then
Range("M13").Interior.ColorIndex = 36
End If
If Target.Address = "$M$16" Then
Range("M16").Interior.ColorIndex = 36
End If
If Target.Address = "$M$17" Then
Range("M17").Interior.ColorIndex = 36
End If
If Target.Address = "$M$21" Then
Range("M21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$4" Then
Range("Q13").Interior.ColorIndex = 3
Range("Q16").Interior.ColorIndex = 3
Range("Q17").Interior.ColorIndex = 3
Range("Q21").Interior.ColorIndex = 3
Range("Q22").Interior.ColorIndex = 3
End If
If Target.Address = "$Q$13" Then
Range("Q13").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$16" Then
Range("Q16").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$17" Then
Range("Q17").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$21" Then
Range("Q21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$22" Then
Range("Q22").Interior.ColorIndex = 36
End If
ActiveSheet.Protect
End Sub
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
JLuc
Utilise : Select Case Target.Address Case "E4":... Case "E13":... ... End Select
Steve avait soumis l'idée :
Bonjours, Grace à vos conseils j'ai écris ce code qui fonctionne. Je me demande si j'aurai pu mieux faire. Peut être une boucle..... Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Address = "$E$4" Then Range("E13").Interior.ColorIndex = 3 End If If Target.Address = "$E$13" Then Range("E13").Interior.ColorIndex = 36 End If If Target.Address = "$I$4" Then Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 End If If Target.Address = "$I$13" Then Range("i13").Interior.ColorIndex = 36 End If If Target.Address = "$I$21" Then Range("i21").Interior.ColorIndex = 36 End If If Target.Address = "$M$4" Then Range("M13").Interior.ColorIndex = 3 Range("M16").Interior.ColorIndex = 3 Range("M17").Interior.ColorIndex = 3 Range("M21").Interior.ColorIndex = 3 End If If Target.Address = "$M$13" Then Range("M13").Interior.ColorIndex = 36 End If If Target.Address = "$M$16" Then Range("M16").Interior.ColorIndex = 36 End If If Target.Address = "$M$17" Then Range("M17").Interior.ColorIndex = 36 End If If Target.Address = "$M$21" Then Range("M21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$4" Then Range("Q13").Interior.ColorIndex = 3 Range("Q16").Interior.ColorIndex = 3 Range("Q17").Interior.ColorIndex = 3 Range("Q21").Interior.ColorIndex = 3 Range("Q22").Interior.ColorIndex = 3 End If If Target.Address = "$Q$13" Then Range("Q13").Interior.ColorIndex = 36 End If If Target.Address = "$Q$16" Then Range("Q16").Interior.ColorIndex = 36 End If If Target.Address = "$Q$17" Then Range("Q17").Interior.ColorIndex = 36 End If If Target.Address = "$Q$21" Then Range("Q21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$22" Then Range("Q22").Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
-- JLuc
Utilise :
Select Case Target.Address
Case "E4":...
Case "E13":...
...
End Select
Steve avait soumis l'idée :
Bonjours,
Grace à vos conseils j'ai écris ce code qui fonctionne.
Je me demande si j'aurai pu mieux faire.
Peut être une boucle.....
Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Address = "$E$4" Then
Range("E13").Interior.ColorIndex = 3
End If
If Target.Address = "$E$13" Then
Range("E13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$4" Then
Range("i13").Interior.ColorIndex = 3
Range("i21").Interior.ColorIndex = 3
End If
If Target.Address = "$I$13" Then
Range("i13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$21" Then
Range("i21").Interior.ColorIndex = 36
End If
If Target.Address = "$M$4" Then
Range("M13").Interior.ColorIndex = 3
Range("M16").Interior.ColorIndex = 3
Range("M17").Interior.ColorIndex = 3
Range("M21").Interior.ColorIndex = 3
End If
If Target.Address = "$M$13" Then
Range("M13").Interior.ColorIndex = 36
End If
If Target.Address = "$M$16" Then
Range("M16").Interior.ColorIndex = 36
End If
If Target.Address = "$M$17" Then
Range("M17").Interior.ColorIndex = 36
End If
If Target.Address = "$M$21" Then
Range("M21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$4" Then
Range("Q13").Interior.ColorIndex = 3
Range("Q16").Interior.ColorIndex = 3
Range("Q17").Interior.ColorIndex = 3
Range("Q21").Interior.ColorIndex = 3
Range("Q22").Interior.ColorIndex = 3
End If
If Target.Address = "$Q$13" Then
Range("Q13").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$16" Then
Range("Q16").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$17" Then
Range("Q17").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$21" Then
Range("Q21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$22" Then
Range("Q22").Interior.ColorIndex = 36
End If
ActiveSheet.Protect
End Sub
Utilise : Select Case Target.Address Case "E4":... Case "E13":... ... End Select
Steve avait soumis l'idée :
Bonjours, Grace à vos conseils j'ai écris ce code qui fonctionne. Je me demande si j'aurai pu mieux faire. Peut être une boucle..... Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Address = "$E$4" Then Range("E13").Interior.ColorIndex = 3 End If If Target.Address = "$E$13" Then Range("E13").Interior.ColorIndex = 36 End If If Target.Address = "$I$4" Then Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 End If If Target.Address = "$I$13" Then Range("i13").Interior.ColorIndex = 36 End If If Target.Address = "$I$21" Then Range("i21").Interior.ColorIndex = 36 End If If Target.Address = "$M$4" Then Range("M13").Interior.ColorIndex = 3 Range("M16").Interior.ColorIndex = 3 Range("M17").Interior.ColorIndex = 3 Range("M21").Interior.ColorIndex = 3 End If If Target.Address = "$M$13" Then Range("M13").Interior.ColorIndex = 36 End If If Target.Address = "$M$16" Then Range("M16").Interior.ColorIndex = 36 End If If Target.Address = "$M$17" Then Range("M17").Interior.ColorIndex = 36 End If If Target.Address = "$M$21" Then Range("M21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$4" Then Range("Q13").Interior.ColorIndex = 3 Range("Q16").Interior.ColorIndex = 3 Range("Q17").Interior.ColorIndex = 3 Range("Q21").Interior.ColorIndex = 3 Range("Q22").Interior.ColorIndex = 3 End If If Target.Address = "$Q$13" Then Range("Q13").Interior.ColorIndex = 36 End If If Target.Address = "$Q$16" Then Range("Q16").Interior.ColorIndex = 36 End If If Target.Address = "$Q$17" Then Range("Q17").Interior.ColorIndex = 36 End If If Target.Address = "$Q$21" Then Range("Q21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$22" Then Range("Q22").Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
-- JLuc
Daniel
Bonjour. Tu eusses peut-être pu utiliser "select case" au lieu de ta batterie de "if", ou alors ajouter devant chaque "end if" les commandes "activesheet.protect" et "exit sub" qui évitent de faire les tests inutiles. Autre détail, Range("I13,I21").Interior.ColorIndex = 3 peut remplacer : Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 Cordialement. Daniel
"Steve" a écrit dans le message de news:
Bonjours, Grace à vos conseils j'ai écris ce code qui fonctionne. Je me demande si j'aurai pu mieux faire. Peut être une boucle..... Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Address = "$E$4" Then Range("E13").Interior.ColorIndex = 3 End If If Target.Address = "$E$13" Then Range("E13").Interior.ColorIndex = 36 End If If Target.Address = "$I$4" Then Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 End If If Target.Address = "$I$13" Then Range("i13").Interior.ColorIndex = 36 End If If Target.Address = "$I$21" Then Range("i21").Interior.ColorIndex = 36 End If If Target.Address = "$M$4" Then Range("M13").Interior.ColorIndex = 3 Range("M16").Interior.ColorIndex = 3 Range("M17").Interior.ColorIndex = 3 Range("M21").Interior.ColorIndex = 3 End If If Target.Address = "$M$13" Then Range("M13").Interior.ColorIndex = 36 End If If Target.Address = "$M$16" Then Range("M16").Interior.ColorIndex = 36 End If If Target.Address = "$M$17" Then Range("M17").Interior.ColorIndex = 36 End If If Target.Address = "$M$21" Then Range("M21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$4" Then Range("Q13").Interior.ColorIndex = 3 Range("Q16").Interior.ColorIndex = 3 Range("Q17").Interior.ColorIndex = 3 Range("Q21").Interior.ColorIndex = 3 Range("Q22").Interior.ColorIndex = 3 End If If Target.Address = "$Q$13" Then Range("Q13").Interior.ColorIndex = 36 End If If Target.Address = "$Q$16" Then Range("Q16").Interior.ColorIndex = 36 End If If Target.Address = "$Q$17" Then Range("Q17").Interior.ColorIndex = 36 End If If Target.Address = "$Q$21" Then Range("Q21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$22" Then Range("Q22").Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
Bonjour.
Tu eusses peut-être pu utiliser "select case" au lieu de ta batterie de
"if", ou alors ajouter devant chaque "end if" les commandes
"activesheet.protect" et "exit sub" qui évitent de faire les tests inutiles.
Autre détail, Range("I13,I21").Interior.ColorIndex = 3 peut remplacer :
Range("i13").Interior.ColorIndex = 3
Range("i21").Interior.ColorIndex = 3
Cordialement.
Daniel
"Steve" <Steve@9online.fr> a écrit dans le message de news:
eAlhAFDHGHA.1288@TK2MSFTNGP09.phx.gbl...
Bonjours,
Grace à vos conseils j'ai écris ce code qui fonctionne.
Je me demande si j'aurai pu mieux faire.
Peut être une boucle.....
Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Address = "$E$4" Then
Range("E13").Interior.ColorIndex = 3
End If
If Target.Address = "$E$13" Then
Range("E13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$4" Then
Range("i13").Interior.ColorIndex = 3
Range("i21").Interior.ColorIndex = 3
End If
If Target.Address = "$I$13" Then
Range("i13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$21" Then
Range("i21").Interior.ColorIndex = 36
End If
If Target.Address = "$M$4" Then
Range("M13").Interior.ColorIndex = 3
Range("M16").Interior.ColorIndex = 3
Range("M17").Interior.ColorIndex = 3
Range("M21").Interior.ColorIndex = 3
End If
If Target.Address = "$M$13" Then
Range("M13").Interior.ColorIndex = 36
End If
If Target.Address = "$M$16" Then
Range("M16").Interior.ColorIndex = 36
End If
If Target.Address = "$M$17" Then
Range("M17").Interior.ColorIndex = 36
End If
If Target.Address = "$M$21" Then
Range("M21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$4" Then
Range("Q13").Interior.ColorIndex = 3
Range("Q16").Interior.ColorIndex = 3
Range("Q17").Interior.ColorIndex = 3
Range("Q21").Interior.ColorIndex = 3
Range("Q22").Interior.ColorIndex = 3
End If
If Target.Address = "$Q$13" Then
Range("Q13").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$16" Then
Range("Q16").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$17" Then
Range("Q17").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$21" Then
Range("Q21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$22" Then
Range("Q22").Interior.ColorIndex = 36
End If
ActiveSheet.Protect
End Sub
Bonjour. Tu eusses peut-être pu utiliser "select case" au lieu de ta batterie de "if", ou alors ajouter devant chaque "end if" les commandes "activesheet.protect" et "exit sub" qui évitent de faire les tests inutiles. Autre détail, Range("I13,I21").Interior.ColorIndex = 3 peut remplacer : Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 Cordialement. Daniel
"Steve" a écrit dans le message de news:
Bonjours, Grace à vos conseils j'ai écris ce code qui fonctionne. Je me demande si j'aurai pu mieux faire. Peut être une boucle..... Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Address = "$E$4" Then Range("E13").Interior.ColorIndex = 3 End If If Target.Address = "$E$13" Then Range("E13").Interior.ColorIndex = 36 End If If Target.Address = "$I$4" Then Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 End If If Target.Address = "$I$13" Then Range("i13").Interior.ColorIndex = 36 End If If Target.Address = "$I$21" Then Range("i21").Interior.ColorIndex = 36 End If If Target.Address = "$M$4" Then Range("M13").Interior.ColorIndex = 3 Range("M16").Interior.ColorIndex = 3 Range("M17").Interior.ColorIndex = 3 Range("M21").Interior.ColorIndex = 3 End If If Target.Address = "$M$13" Then Range("M13").Interior.ColorIndex = 36 End If If Target.Address = "$M$16" Then Range("M16").Interior.ColorIndex = 36 End If If Target.Address = "$M$17" Then Range("M17").Interior.ColorIndex = 36 End If If Target.Address = "$M$21" Then Range("M21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$4" Then Range("Q13").Interior.ColorIndex = 3 Range("Q16").Interior.ColorIndex = 3 Range("Q17").Interior.ColorIndex = 3 Range("Q21").Interior.ColorIndex = 3 Range("Q22").Interior.ColorIndex = 3 End If If Target.Address = "$Q$13" Then Range("Q13").Interior.ColorIndex = 36 End If If Target.Address = "$Q$16" Then Range("Q16").Interior.ColorIndex = 36 End If If Target.Address = "$Q$17" Then Range("Q17").Interior.ColorIndex = 36 End If If Target.Address = "$Q$21" Then Range("Q21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$22" Then Range("Q22").Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
Steve
Merci Jean Luc, Merci Daniel, vos conseils me font progresser Bonsoir et A+
"Steve" a écrit dans le message de news:
Bonjours, Grace à vos conseils j'ai écris ce code qui fonctionne. Je me demande si j'aurai pu mieux faire. Peut être une boucle..... Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Address = "$E$4" Then Range("E13").Interior.ColorIndex = 3 End If If Target.Address = "$E$13" Then Range("E13").Interior.ColorIndex = 36 End If If Target.Address = "$I$4" Then Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 End If If Target.Address = "$I$13" Then Range("i13").Interior.ColorIndex = 36 End If If Target.Address = "$I$21" Then Range("i21").Interior.ColorIndex = 36 End If If Target.Address = "$M$4" Then Range("M13").Interior.ColorIndex = 3 Range("M16").Interior.ColorIndex = 3 Range("M17").Interior.ColorIndex = 3 Range("M21").Interior.ColorIndex = 3 End If If Target.Address = "$M$13" Then Range("M13").Interior.ColorIndex = 36 End If If Target.Address = "$M$16" Then Range("M16").Interior.ColorIndex = 36 End If If Target.Address = "$M$17" Then Range("M17").Interior.ColorIndex = 36 End If If Target.Address = "$M$21" Then Range("M21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$4" Then Range("Q13").Interior.ColorIndex = 3 Range("Q16").Interior.ColorIndex = 3 Range("Q17").Interior.ColorIndex = 3 Range("Q21").Interior.ColorIndex = 3 Range("Q22").Interior.ColorIndex = 3 End If If Target.Address = "$Q$13" Then Range("Q13").Interior.ColorIndex = 36 End If If Target.Address = "$Q$16" Then Range("Q16").Interior.ColorIndex = 36 End If If Target.Address = "$Q$17" Then Range("Q17").Interior.ColorIndex = 36 End If If Target.Address = "$Q$21" Then Range("Q21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$22" Then Range("Q22").Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
Merci Jean Luc,
Merci Daniel,
vos conseils me font progresser
Bonsoir et A+
"Steve" <Steve@9online.fr> a écrit dans le message de news:
eAlhAFDHGHA.1288@TK2MSFTNGP09.phx.gbl...
Bonjours,
Grace à vos conseils j'ai écris ce code qui fonctionne.
Je me demande si j'aurai pu mieux faire.
Peut être une boucle.....
Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Address = "$E$4" Then
Range("E13").Interior.ColorIndex = 3
End If
If Target.Address = "$E$13" Then
Range("E13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$4" Then
Range("i13").Interior.ColorIndex = 3
Range("i21").Interior.ColorIndex = 3
End If
If Target.Address = "$I$13" Then
Range("i13").Interior.ColorIndex = 36
End If
If Target.Address = "$I$21" Then
Range("i21").Interior.ColorIndex = 36
End If
If Target.Address = "$M$4" Then
Range("M13").Interior.ColorIndex = 3
Range("M16").Interior.ColorIndex = 3
Range("M17").Interior.ColorIndex = 3
Range("M21").Interior.ColorIndex = 3
End If
If Target.Address = "$M$13" Then
Range("M13").Interior.ColorIndex = 36
End If
If Target.Address = "$M$16" Then
Range("M16").Interior.ColorIndex = 36
End If
If Target.Address = "$M$17" Then
Range("M17").Interior.ColorIndex = 36
End If
If Target.Address = "$M$21" Then
Range("M21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$4" Then
Range("Q13").Interior.ColorIndex = 3
Range("Q16").Interior.ColorIndex = 3
Range("Q17").Interior.ColorIndex = 3
Range("Q21").Interior.ColorIndex = 3
Range("Q22").Interior.ColorIndex = 3
End If
If Target.Address = "$Q$13" Then
Range("Q13").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$16" Then
Range("Q16").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$17" Then
Range("Q17").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$21" Then
Range("Q21").Interior.ColorIndex = 36
End If
If Target.Address = "$Q$22" Then
Range("Q22").Interior.ColorIndex = 36
End If
ActiveSheet.Protect
End Sub
Merci Jean Luc, Merci Daniel, vos conseils me font progresser Bonsoir et A+
"Steve" a écrit dans le message de news:
Bonjours, Grace à vos conseils j'ai écris ce code qui fonctionne. Je me demande si j'aurai pu mieux faire. Peut être une boucle..... Merci de vos conseils.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Address = "$E$4" Then Range("E13").Interior.ColorIndex = 3 End If If Target.Address = "$E$13" Then Range("E13").Interior.ColorIndex = 36 End If If Target.Address = "$I$4" Then Range("i13").Interior.ColorIndex = 3 Range("i21").Interior.ColorIndex = 3 End If If Target.Address = "$I$13" Then Range("i13").Interior.ColorIndex = 36 End If If Target.Address = "$I$21" Then Range("i21").Interior.ColorIndex = 36 End If If Target.Address = "$M$4" Then Range("M13").Interior.ColorIndex = 3 Range("M16").Interior.ColorIndex = 3 Range("M17").Interior.ColorIndex = 3 Range("M21").Interior.ColorIndex = 3 End If If Target.Address = "$M$13" Then Range("M13").Interior.ColorIndex = 36 End If If Target.Address = "$M$16" Then Range("M16").Interior.ColorIndex = 36 End If If Target.Address = "$M$17" Then Range("M17").Interior.ColorIndex = 36 End If If Target.Address = "$M$21" Then Range("M21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$4" Then Range("Q13").Interior.ColorIndex = 3 Range("Q16").Interior.ColorIndex = 3 Range("Q17").Interior.ColorIndex = 3 Range("Q21").Interior.ColorIndex = 3 Range("Q22").Interior.ColorIndex = 3 End If If Target.Address = "$Q$13" Then Range("Q13").Interior.ColorIndex = 36 End If If Target.Address = "$Q$16" Then Range("Q16").Interior.ColorIndex = 36 End If If Target.Address = "$Q$17" Then Range("Q17").Interior.ColorIndex = 36 End If If Target.Address = "$Q$21" Then Range("Q21").Interior.ColorIndex = 36 End If If Target.Address = "$Q$22" Then Range("Q22").Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
JB
Bonjour,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Union([M16], [M17], [M21], [Q13], [Q16], [Q17])) Is Nothing And Target.Count = 1 Then Target.Interior.ColorIndex = 36 End If End Sub
Cordialement JB
Bonjour,
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union([M16], [M17], [M21], [Q13], [Q16],
[Q17])) Is Nothing And Target.Count = 1 Then
Target.Interior.ColorIndex = 36
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Union([M16], [M17], [M21], [Q13], [Q16], [Q17])) Is Nothing And Target.Count = 1 Then Target.Interior.ColorIndex = 36 End If End Sub
Cordialement JB
Steve
Merci JB A+ Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect Select Case Target.Address Case "$E$4": Range("E13").Interior.ColorIndex = 3 Case "$I$4": Range("I13,I21").Interior.ColorIndex = 3 Case "$M$4": Range("M13,M16,M17,M21").Interior.ColorIndex = 3 Case "$Q$4": Range("Q13,Q16,Q17,Q21,Q22").Interior.ColorIndex = 3 End Select If Not Intersect(Target, Union([E13], [I13], [I21], [M13], [M16], [M17], [M21], [Q13], [Q16], [Q17], [Q21], [Q22])) Is Nothing And Target.Count = 1 Then Target.Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
"JB" a écrit dans le message de news:
Bonjour,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Union([M16], [M17], [M21], [Q13], [Q16], [Q17])) Is Nothing And Target.Count = 1 Then Target.Interior.ColorIndex = 36 End If End Sub
Cordialement JB
Merci JB
A+
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
Select Case Target.Address
Case "$E$4": Range("E13").Interior.ColorIndex = 3
Case "$I$4": Range("I13,I21").Interior.ColorIndex = 3
Case "$M$4": Range("M13,M16,M17,M21").Interior.ColorIndex = 3
Case "$Q$4": Range("Q13,Q16,Q17,Q21,Q22").Interior.ColorIndex = 3
End Select
If Not Intersect(Target, Union([E13], [I13], [I21], [M13], [M16], [M17],
[M21],
[Q13], [Q16], [Q17], [Q21], [Q22])) Is Nothing And Target.Count = 1 Then
Target.Interior.ColorIndex = 36
End If
ActiveSheet.Protect
End Sub
"JB" <boisgontier@hotmail.com> a écrit dans le message de news:
1137641863.086866.227790@g14g2000cwa.googlegroups.com...
Bonjour,
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union([M16], [M17], [M21], [Q13], [Q16],
[Q17])) Is Nothing And Target.Count = 1 Then
Target.Interior.ColorIndex = 36
End If
End Sub
Merci JB A+ Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect Select Case Target.Address Case "$E$4": Range("E13").Interior.ColorIndex = 3 Case "$I$4": Range("I13,I21").Interior.ColorIndex = 3 Case "$M$4": Range("M13,M16,M17,M21").Interior.ColorIndex = 3 Case "$Q$4": Range("Q13,Q16,Q17,Q21,Q22").Interior.ColorIndex = 3 End Select If Not Intersect(Target, Union([E13], [I13], [I21], [M13], [M16], [M17], [M21], [Q13], [Q16], [Q17], [Q21], [Q22])) Is Nothing And Target.Count = 1 Then Target.Interior.ColorIndex = 36 End If ActiveSheet.Protect End Sub
"JB" a écrit dans le message de news:
Bonjour,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Union([M16], [M17], [M21], [Q13], [Q16], [Q17])) Is Nothing And Target.Count = 1 Then Target.Interior.ColorIndex = 36 End If End Sub