1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
|
Set ws11 = Sheets("Controle")
Set plage11 = ws11.Range("A4:A39")
With ws11
'Changement couleur ligne suivant place
For Plage = 4 To 39
If ws11.Range("A" & Plage) = "" Then
ws11.Range("A" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("A" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("B" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("B" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("C" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("C" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("D" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("D" & Plage).Interior.Color = RGB(255, 255, 255)
ElseIf ws11.Range("D" & Plage) < 0 Then
ws11.Range("A" & Plage).Font.Color = RGB(153, 0, 204)
ws11.Range("A" & Plage).Interior.Color = RGB(255, 153, 255)
ws11.Range("B" & Plage).Font.Color = RGB(153, 0, 204)
ws11.Range("B" & Plage).Interior.Color = RGB(255, 153, 255)
ws11.Range("C" & Plage).Font.Color = RGB(153, 0, 204)
ws11.Range("C" & Plage).Interior.Color = RGB(255, 153, 255)
ws11.Range("D" & Plage).Font.Color = RGB(153, 0, 204)
ws11.Range("D" & Plage).Interior.Color = RGB(255, 153, 255)
ElseIf ws11.Range("D" & Plage) = 0 Or ws11.Range("D" & Plage) = "" Then
ws11.Range("A" & Plage).Font.Color = RGB(156, 0, 6)
ws11.Range("A" & Plage).Interior.Color = RGB(255, 199, 206)
ws11.Range("B" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("B" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("C" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("C" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("D" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("D" & Plage).Interior.Color = RGB(255, 255, 255)
Else
ws11.Range("A" & Plage).Font.Color = RGB(0, 97, 0)
ws11.Range("A" & Plage).Interior.Color = RGB(198, 239, 206)
ws11.Range("B" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("B" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("C" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("C" & Plage).Interior.Color = RGB(255, 255, 255)
ws11.Range("D" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("D" & Plage).Interior.Color = RGB(255, 255, 255)
End If
Next Plage
'Changement couleur ligne suivant age et apte
For Plage = 4 To 27
If ws11.Range("G" & Plage) = "" Then
ws11.Range("G" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("G" & Plage).Interior.Color = RGB(255, 255, 255)
ElseIf ws11.Range("G" & Plage) <= 17 Then
ws11.Range("G" & Plage).Font.Color = RGB(156, 0, 6)
ws11.Range("G" & Plage).Interior.Color = RGB(255, 199, 206)
Else
ws11.Range("G" & Plage).Font.Color = RGB(0, 97, 0)
ws11.Range("G" & Plage).Interior.Color = RGB(198, 239, 206)
End If
If ws11.Range("F" & Plage) = "" Then
ws11.Range("H" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("H" & Plage).Interior.Color = RGB(255, 255, 255)
ElseIf ws11.Range("H" & Plage) = "" Then
ws11.Range("H" & Plage).Font.Color = RGB(156, 0, 6)
ws11.Range("H" & Plage).Interior.Color = RGB(255, 199, 206)
Else
ws11.Range("H" & Plage).Font.Color = RGB(0, 97, 0)
ws11.Range("H" & Plage).Interior.Color = RGB(198, 239, 206)
End If
'affichage doublon résultat négatif
matchRow = Application.Match(ws11.Range("J" & Plage).Value, plage11, 0)
isFound = Not IsError(matchRow)
If isFound Then
isValuePositive = ws11.Range("D" & plage11.Cells(matchRow, 1).Row).Value >= 0
isValueRien = ws11.Range("D" & plage11.Cells(matchRow, 1).Row).Value <> ""
Else
isValuePositive = False
isValueRien = False
End If
If (isFound And isValuePositive And isValueRien) Or ws11.Range("J" & Plage) = "" Then
ws11.Range("J" & Plage).Font.Color = RGB(0, 0, 0)
ws11.Range("J" & Plage).Interior.Color = RGB(255, 255, 255)
Else
ws11.Range("J" & Plage).Font.Color = RGB(153, 0, 204)
ws11.Range("J" & Plage).Interior.Color = RGB(255, 153, 255)
End If
Next Plage
If ws11.Range("D3") < 0 Then
ws11.Range("D2", "D3").Font.Color = RGB(153, 0, 204)
ws11.Range("D2", "D3").Interior.Color = RGB(255, 153, 255)
Else
ws11.Range("D2", "D3").Font.Color = RGB(0, 0, 0)
ws11.Range("D2", "D3").Interior.Color = RGB(255, 255, 255)
End If
End With |
Partager