J
joris
Guest
Bonjour a tous,
j'ai une petite question au sujet de la charge de travail supporter par excel.
En effet commencant tout juste le VB je commence a ecrire des macros qui deviennent de plus en plus consequentes et qui ressembleront bientot a un vrai programme en C. Seulement n'etant pas excellent en optimisation de mon code mes macros commence deja a ramer...
Par exemplen'ayez pas peur je sais c'est pas tres beau)
'this is to change automatically the color of the cells depends of the valor
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Err = 0
'this is to search the line of the different risk area
Recherche1 = "1. Contractual clauses"
Recherche2 = "2. Price"
Recherche3 = "3. System Engineering"
Recherche4 = "4. Hardware/Software Development, Technology"
Recherche5 = "5. Logistics"
Recherche6 = "6. Production"
Recherche7 = "7. Procurements"
Recherche8 = "8. Program Management"
Recherche9 = "9. Partners/Sub-contractors"
Recherche10 = "10. Customer"
Recherche11 = "11. Competitors"
Recherche12 = "12. Strategy"
ligne1 = Cells.Find(Recherche1, LookIn:=xlValues).Row
ligne2 = Cells.Find(Recherche2, LookIn:=xlValues).Row
ligne3 = Cells.Find(Recherche3, LookIn:=xlValues).Row
ligne4 = Cells.Find(Recherche4, LookIn:=xlValues).Row
ligne5 = Cells.Find(Recherche5, LookIn:=xlValues).Row
ligne6 = Cells.Find(Recherche6, LookIn:=xlValues).Row
ligne7 = Cells.Find(Recherche7, LookIn:=xlValues).Row
ligne8 = Cells.Find(Recherche8, LookIn:=xlValues).Row
ligne9 = Cells.Find(Recherche9, LookIn:=xlValues).Row
ligne10 = Cells.Find(Recherche10, LookIn:=xlValues).Row
ligne11 = Cells.Find(Recherche11, LookIn:=xlValues).Row
ligne12 = Cells.Find(Recherche12, LookIn:=xlValues).Row
If Err <> 0 Then
Sheet2.Range("aF1") = "test"
Else
Sheet2.Range("aF1") = "test1"
End If
Dim Nol1 As Integer
Dim Nol As Integer
'find the end of the document
Nol = Range("O65536").End(xlUp).Row
Nol1 = Range("Q65536").End(xlUp).Row
'color of the cells
For Nol = 5 To Nol
If Range("O" & Nol) < "0.05" And Range("O" & Nol) > "0" Then
Range("O" & Nol).Interior.ColorIndex = 15
ElseIf Range("O" & Nol) >= "0.05" And Range("O" & Nol) <= "0.14" Then
Range("O" & Nol).Interior.ColorIndex = 8
ElseIf Range("O" & Nol) >= "0.18" And Range("O" & Nol) <= "0.72" Then
Range("O" & Nol).Interior.ColorIndex = 6
ElseIf Range("O" & Nol) < "0" And Range("O" & Nol) >= "-0.04" Then
Range("O" & Nol).Interior.ColorIndex = 15
ElseIf Range("O" & Nol) <= "-0.05" And Range("O" & Nol) >= "-0.14" Then
Range("O" & Nol).Interior.ColorIndex = 8
ElseIf Range("O" & Nol) <= "-0.18" And Range("O" & Nol) >= "-0.72" Then
Range("O" & Nol).Interior.ColorIndex = 6
ElseIf Nol = ligne1 Or Nol = ligne2 Or Nol = ligne3 Or Nol = ligne4 Or Nol = ligne5 Or Nol = ligne6 Or Nol = ligne7 Or Nol = ligne8 Or Nol = ligne9 Or Nol = ligne10 Or Nol = ligne11 Or Nol = ligne12 Then
Range("O" & Nol).Interior.ColorIndex = 20
Else
Range("O" & Nol).Interior.ColorIndex = 2
End If
If Range("N" & Nol) < "0.05" And Range("N" & Nol) > "0" Then
Range("N" & Nol).Interior.ColorIndex = 15
ElseIf Range("N" & Nol) >= "0.05" And Range("N" & Nol) <= "0.14" Then
Range("N" & Nol).Interior.ColorIndex = 8
ElseIf Range("N" & Nol) >= "0.18" And Range("N" & Nol) <= "0.72" Then
Range("N" & Nol).Interior.ColorIndex = 6
ElseIf Range("N" & Nol) < "0" And Range("N" & Nol) >= "-0.04" Then
Range("N" & Nol).Interior.ColorIndex = 15
ElseIf Range("N" & Nol) <= "-0.05" And Range("N" & Nol) >= "-0.14" Then
Range("N" & Nol).Interior.ColorIndex = 8
ElseIf Range("N" & Nol) <= "-0.18" And Range("N" & Nol) >= "-0.72" Then
Range("N" & Nol).Interior.ColorIndex = 6
ElseIf Nol = ligne1 Or Nol = ligne2 Or Nol = ligne3 Or Nol = ligne4 Or Nol = ligne5 Or Nol = ligne6 Or Nol = ligne7 Or Nol = ligne8 Or Nol = ligne9 Or Nol = ligne10 Or Nol = ligne11 Or Nol = ligne12 Then
Range("N" & Nol).Interior.ColorIndex = 20
Else
Range("N" & Nol).Interior.ColorIndex = 2
End If
If Range("P" & Nol) < "0.05" And Range("P" & Nol) > "0" Then
Range("P" & Nol).Interior.ColorIndex = 15
ElseIf Range("P" & Nol) >= "0.05" And Range("P" & Nol) <= "0.14" Then
Range("P" & Nol).Interior.ColorIndex = 8
ElseIf Range("P" & Nol) >= "0.18" And Range("P" & Nol) <= "0.72" Then
Range("P" & Nol).Interior.ColorIndex = 6
ElseIf Range("P" & Nol) < "0" And Range("P" & Nol) >= "-0.04" Then
Range("P" & Nol).Interior.ColorIndex = 15
ElseIf Range("P" & Nol) <= "-0.05" And Range("P" & Nol) >= "-0.14" Then
Range("P" & Nol).Interior.ColorIndex = 8
ElseIf Range("P" & Nol) <= "-0.18" And Range("P" & Nol) >= "-0.72" Then
Range("P" & Nol).Interior.ColorIndex = 6
ElseIf Nol = ligne1 Or Nol = ligne2 Or Nol = ligne3 Or Nol = ligne4 Or Nol = ligne5 Or Nol = ligne6 Or Nol = ligne7 Or Nol = ligne8 Or Nol = ligne9 Or Nol = ligne10 Or Nol = ligne11 Or Nol = ligne12 Then
Range("P" & Nol).Interior.ColorIndex = 20
Else
Range("P" & Nol).Interior.ColorIndex = 2
End If
Next
For Nol1 = 5 To Nol1
If Range("Q" & Nol1) = "low" Then
Range("Q" & Nol1).Interior.ColorIndex = 15
ElseIf Range("Q" & Nol1) = "moderate" Then
Range("Q" & Nol1).Interior.ColorIndex = 8
ElseIf Range("Q" & Nol1) = "high" Then
Range("Q" & Nol1).Interior.ColorIndex = 6
ElseIf Range("Q" & Nol1) = "extreme" Then
Range("Q" & Nol1).Interior.ColorIndex = 3
ElseIf Nol1 = ligne1 Or Nol1 = ligne2 Or Nol1 = ligne3 Or Nol1 = ligne4 Or Nol1 = ligne5 Or Nol1 = ligne6 Or Nol1 = ligne7 Or Nol1 = ligne8 Or Nol1 = ligne9 Or Nol1 = ligne10 Or Nol1 = ligne11 Or Nol1 = ligne12 Then
Range("Q" & Nol1).Interior.ColorIndex = 20
Else
Range("Q" & Nol1).Interior.ColorIndex = 2
End If
Next
End Sub
seulemtn maintenant des que je touche a ma page il rame grave car il reparcours les trois colonnes O P et N pour changer les couleurs des cellules...
Qq peut me renseigner donc sur la rapidite d'execution du VBA et s'il faut donc que je repense mon code.
Merci
Joris
j'ai une petite question au sujet de la charge de travail supporter par excel.
En effet commencant tout juste le VB je commence a ecrire des macros qui deviennent de plus en plus consequentes et qui ressembleront bientot a un vrai programme en C. Seulement n'etant pas excellent en optimisation de mon code mes macros commence deja a ramer...
Par exemplen'ayez pas peur je sais c'est pas tres beau)
'this is to change automatically the color of the cells depends of the valor
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Err = 0
'this is to search the line of the different risk area
Recherche1 = "1. Contractual clauses"
Recherche2 = "2. Price"
Recherche3 = "3. System Engineering"
Recherche4 = "4. Hardware/Software Development, Technology"
Recherche5 = "5. Logistics"
Recherche6 = "6. Production"
Recherche7 = "7. Procurements"
Recherche8 = "8. Program Management"
Recherche9 = "9. Partners/Sub-contractors"
Recherche10 = "10. Customer"
Recherche11 = "11. Competitors"
Recherche12 = "12. Strategy"
ligne1 = Cells.Find(Recherche1, LookIn:=xlValues).Row
ligne2 = Cells.Find(Recherche2, LookIn:=xlValues).Row
ligne3 = Cells.Find(Recherche3, LookIn:=xlValues).Row
ligne4 = Cells.Find(Recherche4, LookIn:=xlValues).Row
ligne5 = Cells.Find(Recherche5, LookIn:=xlValues).Row
ligne6 = Cells.Find(Recherche6, LookIn:=xlValues).Row
ligne7 = Cells.Find(Recherche7, LookIn:=xlValues).Row
ligne8 = Cells.Find(Recherche8, LookIn:=xlValues).Row
ligne9 = Cells.Find(Recherche9, LookIn:=xlValues).Row
ligne10 = Cells.Find(Recherche10, LookIn:=xlValues).Row
ligne11 = Cells.Find(Recherche11, LookIn:=xlValues).Row
ligne12 = Cells.Find(Recherche12, LookIn:=xlValues).Row
If Err <> 0 Then
Sheet2.Range("aF1") = "test"
Else
Sheet2.Range("aF1") = "test1"
End If
Dim Nol1 As Integer
Dim Nol As Integer
'find the end of the document
Nol = Range("O65536").End(xlUp).Row
Nol1 = Range("Q65536").End(xlUp).Row
'color of the cells
For Nol = 5 To Nol
If Range("O" & Nol) < "0.05" And Range("O" & Nol) > "0" Then
Range("O" & Nol).Interior.ColorIndex = 15
ElseIf Range("O" & Nol) >= "0.05" And Range("O" & Nol) <= "0.14" Then
Range("O" & Nol).Interior.ColorIndex = 8
ElseIf Range("O" & Nol) >= "0.18" And Range("O" & Nol) <= "0.72" Then
Range("O" & Nol).Interior.ColorIndex = 6
ElseIf Range("O" & Nol) < "0" And Range("O" & Nol) >= "-0.04" Then
Range("O" & Nol).Interior.ColorIndex = 15
ElseIf Range("O" & Nol) <= "-0.05" And Range("O" & Nol) >= "-0.14" Then
Range("O" & Nol).Interior.ColorIndex = 8
ElseIf Range("O" & Nol) <= "-0.18" And Range("O" & Nol) >= "-0.72" Then
Range("O" & Nol).Interior.ColorIndex = 6
ElseIf Nol = ligne1 Or Nol = ligne2 Or Nol = ligne3 Or Nol = ligne4 Or Nol = ligne5 Or Nol = ligne6 Or Nol = ligne7 Or Nol = ligne8 Or Nol = ligne9 Or Nol = ligne10 Or Nol = ligne11 Or Nol = ligne12 Then
Range("O" & Nol).Interior.ColorIndex = 20
Else
Range("O" & Nol).Interior.ColorIndex = 2
End If
If Range("N" & Nol) < "0.05" And Range("N" & Nol) > "0" Then
Range("N" & Nol).Interior.ColorIndex = 15
ElseIf Range("N" & Nol) >= "0.05" And Range("N" & Nol) <= "0.14" Then
Range("N" & Nol).Interior.ColorIndex = 8
ElseIf Range("N" & Nol) >= "0.18" And Range("N" & Nol) <= "0.72" Then
Range("N" & Nol).Interior.ColorIndex = 6
ElseIf Range("N" & Nol) < "0" And Range("N" & Nol) >= "-0.04" Then
Range("N" & Nol).Interior.ColorIndex = 15
ElseIf Range("N" & Nol) <= "-0.05" And Range("N" & Nol) >= "-0.14" Then
Range("N" & Nol).Interior.ColorIndex = 8
ElseIf Range("N" & Nol) <= "-0.18" And Range("N" & Nol) >= "-0.72" Then
Range("N" & Nol).Interior.ColorIndex = 6
ElseIf Nol = ligne1 Or Nol = ligne2 Or Nol = ligne3 Or Nol = ligne4 Or Nol = ligne5 Or Nol = ligne6 Or Nol = ligne7 Or Nol = ligne8 Or Nol = ligne9 Or Nol = ligne10 Or Nol = ligne11 Or Nol = ligne12 Then
Range("N" & Nol).Interior.ColorIndex = 20
Else
Range("N" & Nol).Interior.ColorIndex = 2
End If
If Range("P" & Nol) < "0.05" And Range("P" & Nol) > "0" Then
Range("P" & Nol).Interior.ColorIndex = 15
ElseIf Range("P" & Nol) >= "0.05" And Range("P" & Nol) <= "0.14" Then
Range("P" & Nol).Interior.ColorIndex = 8
ElseIf Range("P" & Nol) >= "0.18" And Range("P" & Nol) <= "0.72" Then
Range("P" & Nol).Interior.ColorIndex = 6
ElseIf Range("P" & Nol) < "0" And Range("P" & Nol) >= "-0.04" Then
Range("P" & Nol).Interior.ColorIndex = 15
ElseIf Range("P" & Nol) <= "-0.05" And Range("P" & Nol) >= "-0.14" Then
Range("P" & Nol).Interior.ColorIndex = 8
ElseIf Range("P" & Nol) <= "-0.18" And Range("P" & Nol) >= "-0.72" Then
Range("P" & Nol).Interior.ColorIndex = 6
ElseIf Nol = ligne1 Or Nol = ligne2 Or Nol = ligne3 Or Nol = ligne4 Or Nol = ligne5 Or Nol = ligne6 Or Nol = ligne7 Or Nol = ligne8 Or Nol = ligne9 Or Nol = ligne10 Or Nol = ligne11 Or Nol = ligne12 Then
Range("P" & Nol).Interior.ColorIndex = 20
Else
Range("P" & Nol).Interior.ColorIndex = 2
End If
Next
For Nol1 = 5 To Nol1
If Range("Q" & Nol1) = "low" Then
Range("Q" & Nol1).Interior.ColorIndex = 15
ElseIf Range("Q" & Nol1) = "moderate" Then
Range("Q" & Nol1).Interior.ColorIndex = 8
ElseIf Range("Q" & Nol1) = "high" Then
Range("Q" & Nol1).Interior.ColorIndex = 6
ElseIf Range("Q" & Nol1) = "extreme" Then
Range("Q" & Nol1).Interior.ColorIndex = 3
ElseIf Nol1 = ligne1 Or Nol1 = ligne2 Or Nol1 = ligne3 Or Nol1 = ligne4 Or Nol1 = ligne5 Or Nol1 = ligne6 Or Nol1 = ligne7 Or Nol1 = ligne8 Or Nol1 = ligne9 Or Nol1 = ligne10 Or Nol1 = ligne11 Or Nol1 = ligne12 Then
Range("Q" & Nol1).Interior.ColorIndex = 20
Else
Range("Q" & Nol1).Interior.ColorIndex = 2
End If
Next
End Sub
seulemtn maintenant des que je touche a ma page il rame grave car il reparcours les trois colonnes O P et N pour changer les couleurs des cellules...
Qq peut me renseigner donc sur la rapidite d'execution du VBA et s'il faut donc que je repense mon code.
Merci
Joris