simplifier cette macro

Salut,
Je pense que celà devrait fonctionner


Sub MFC_Colorie_Cellule_Bis()
Dim c As Range
Dim James007 As Boolean
Dim i As Integer, j As Integer
Dim tabColDébut As Variant, tabColFin As Variant
tabColDébut = Array("B", "D", "F", "H", "J", "L")
tabColFin = Array("C", "E", "G", "I", "K", "M")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In ActiveSheet.Range("C2:L34")
James007 = False
For i = LBound(tabColDébut) To UBound(tabColDébut)
For j = 9 To 14
If c >= Sheets("DATES").Range(tabColDébut(i) & j) And c <=
Sheets("DATES").Range(tabColFin(i) & j) Then James007 = True: Exit For
Next j
If James007 = True Then Exit For
Next i
If James007 = True Then c.Interior.ColorIndex = 6 Else
c.Interior.ColorIndex = 0
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


A+
Jc
"Loic" <Loic@discussions.microsoft.com> a écrit dans le message de news:
AF62C038-735F-4467-8417-6BB6ABE0425E@microsoft.com...
| bonjour à tous
|
| apparement cette macro serait simplifiable avec des offset
|
| je me tire les cheveux mais je n'y arrive pas
|
| merci de m'aider
|
|
|
| Sub MFC_Colorie_Cellule()
| ' Macro enregistrée le 25/03/2004 par *******
| Dim c As Range
| For Each c In ActiveSheet.Range("C2:L34")
|
| If c >= Sheets("DATES").Range("B9") And c <=
Sheets("DATES").Range("C9") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("B10") And c < | Sheets("DATES").Range("C10") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("B11") And c < | Sheets("DATES").Range("C11") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("B12") And c < | Sheets("DATES").Range("C12") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("B13") And c < | Sheets("DATES").Range("C13") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("B14") And c < | Sheets("DATES").Range("C14") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("d9") And c <=
Sheets("DATES").Range("e9")
| Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("d10") And c < | Sheets("DATES").Range("e10") Then
| c.Interior.ColorIndex = 6
|
|
| ElseIf c >= Sheets("DATES").Range("d11") And c < | Sheets("DATES").Range("e11") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("d12") And c < | Sheets("DATES").Range("e12") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("d13") And c < | Sheets("DATES").Range("e13") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("d14") And c < | Sheets("DATES").Range("e14") Then
| c.Interior.ColorIndex = 6
|
| ElseIf c >= Sheets("DATES").Range("F9") And c <=
Sheets("DATES").Range("G9")
| Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("F10") And c < | Sheets("DATES").Range("G10") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("F11") And c < | Sheets("DATES").Range("G11") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("F12") And c < | Sheets("DATES").Range("G12") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("F13") And c < | Sheets("DATES").Range("G13") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("F14") And c < | Sheets("DATES").Range("G14") Then
| c.Interior.ColorIndex = 6
|
| ElseIf c >= Sheets("DATES").Range("H9") And c <=
Sheets("DATES").Range("I9")
| Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("H10") And c < | Sheets("DATES").Range("I10") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("H11") And c < | Sheets("DATES").Range("I11") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("H12") And c < | Sheets("DATES").Range("I12") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("H13") And c < | Sheets("DATES").Range("I13") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("H14") And c < | Sheets("DATES").Range("I14") Then
| c.Interior.ColorIndex = 6
|
| ElseIf c >= Sheets("DATES").Range("J9") And c <=
Sheets("DATES").Range("K2")
| Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("J10") And c < | Sheets("DATES").Range("K3") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("J11") And c < | Sheets("DATES").Range("K4") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("J12") And c < | Sheets("DATES").Range("K5") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("J13") And c < | Sheets("DATES").Range("K6") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("J14") And c < | Sheets("DATES").Range("K7") Then
| c.Interior.ColorIndex = 6
|
| ElseIf c >= Sheets("DATES").Range("L9") And c <=
Sheets("DATES").Range("M9")
| Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("L10") And c < | Sheets("DATES").Range("M10") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("L11") And c < | Sheets("DATES").Range("M11") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("L12") And c < | Sheets("DATES").Range("M12") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("L13") And c < | Sheets("DATES").Range("M13") Then
| c.Interior.ColorIndex = 6
| ElseIf c >= Sheets("DATES").Range("L14") And c < | Sheets("DATES").Range("M14") Then
| c.Interior.ColorIndex = 6
|
| Else
| c.Interior.ColorIndex = 0
|
| End If
| Next
| End Sub
|
|
| merci de votre aide

bonjour Loic,

IL y a cette variante :
'------------------------------------
Sub MFC_Colorie_Cellule()
' Macro enregistrée le 25/03/2004 par *******
Dim c As Range, Rg As Range
Dim A as integer, B as integer
Set Rg = Sheets("DATES").Range("B9")
Application.ScreenUpdating = False
For Each c In ActiveSheet.Range("C2:L34")
For a = 1 To 11 Step 2
For b = 1 To 6
Select Case c
Case Is > Rg(b, a) And c <= Rg(b, a + 1)
c.Interior.ColorIndex = 6
Case Else
c.Interior.ColorIndex = 0
End Select
Next
Next
Next
Set Rg = Nothing: Set c = Nothing
End Sub
'------------------------------------


Salutations!

"Loic" <Loic@discussions.microsoft.com> a écrit dans le message de news: AF62C038-735F-4467-8417-6BB6ABE0425E@microsoft.com...
bonjour à tous

apparement cette macro serait simplifiable avec des offset

je me tire les cheveux mais je n'y arrive pas

merci de m'aider



Sub MFC_Colorie_Cellule()
' Macro enregistrée le 25/03/2004 par *******
Dim c As Range
For Each c In ActiveSheet.Range("C2:L34")

If c >= Sheets("DATES").Range("B9") And c <= Sheets("DATES").Range("C9") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B10") And c < Sheets("DATES").Range("C10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B11") And c < Sheets("DATES").Range("C11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B12") And c < Sheets("DATES").Range("C12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B13") And c < Sheets("DATES").Range("C13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B14") And c < Sheets("DATES").Range("C14") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d9") And c <= Sheets("DATES").Range("e9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d10") And c < Sheets("DATES").Range("e10") Then
c.Interior.ColorIndex = 6


ElseIf c >= Sheets("DATES").Range("d11") And c < Sheets("DATES").Range("e11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d12") And c < Sheets("DATES").Range("e12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d13") And c < Sheets("DATES").Range("e13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d14") And c < Sheets("DATES").Range("e14") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("F9") And c <= Sheets("DATES").Range("G9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F10") And c < Sheets("DATES").Range("G10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F11") And c < Sheets("DATES").Range("G11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F12") And c < Sheets("DATES").Range("G12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F13") And c < Sheets("DATES").Range("G13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F14") And c < Sheets("DATES").Range("G14") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("H9") And c <= Sheets("DATES").Range("I9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H10") And c < Sheets("DATES").Range("I10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H11") And c < Sheets("DATES").Range("I11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H12") And c < Sheets("DATES").Range("I12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H13") And c < Sheets("DATES").Range("I13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H14") And c < Sheets("DATES").Range("I14") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("J9") And c <= Sheets("DATES").Range("K2")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J10") And c < Sheets("DATES").Range("K3") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J11") And c < Sheets("DATES").Range("K4") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J12") And c < Sheets("DATES").Range("K5") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J13") And c < Sheets("DATES").Range("K6") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J14") And c < Sheets("DATES").Range("K7") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("L9") And c <= Sheets("DATES").Range("M9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L10") And c < Sheets("DATES").Range("M10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L11") And c < Sheets("DATES").Range("M11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L12") And c < Sheets("DATES").Range("M12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L13") And c < Sheets("DATES").Range("M13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L14") And c < Sheets("DATES").Range("M14") Then
c.Interior.ColorIndex = 6

Else
c.Interior.ColorIndex = 0

End If
Next
End Sub


merci de votre aide

Une toute petite correction :

'-----------------------
Sub MFC_Colorie_Cellule()
' Macro enregistrée le 25/03/2004 par *******
Dim c As Range, Rg As Range
Dim A As Integer, B As Integer
Set Rg = Sheets("Feuil1").Range("B9")
Application.ScreenUpdating = False
ActiveSheet.Range("C2:L34").Interior.ColorIndex = 0
For Each c In ActiveSheet.Range("C2:L34")
For A = 1 To 11 Step 2
For B = 1 To 6
Select Case c
Case Is > Rg(B, A) And c <= Rg(B, A + 1)
c.Interior.ColorIndex = 6
End Select
Next
Next
Next
Set Rg = Nothing: Set c = Nothing
End Sub
'-----------------------


Salutations!



"Loic" <Loic@discussions.microsoft.com> a écrit dans le message de news: AF62C038-735F-4467-8417-6BB6ABE0425E@microsoft.com...
bonjour à tous

apparement cette macro serait simplifiable avec des offset

je me tire les cheveux mais je n'y arrive pas

merci de m'aider



Sub MFC_Colorie_Cellule()
' Macro enregistrée le 25/03/2004 par *******
Dim c As Range
For Each c In ActiveSheet.Range("C2:L34")

If c >= Sheets("DATES").Range("B9") And c <= Sheets("DATES").Range("C9") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B10") And c < Sheets("DATES").Range("C10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B11") And c < Sheets("DATES").Range("C11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B12") And c < Sheets("DATES").Range("C12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B13") And c < Sheets("DATES").Range("C13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("B14") And c < Sheets("DATES").Range("C14") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d9") And c <= Sheets("DATES").Range("e9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d10") And c < Sheets("DATES").Range("e10") Then
c.Interior.ColorIndex = 6


ElseIf c >= Sheets("DATES").Range("d11") And c < Sheets("DATES").Range("e11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d12") And c < Sheets("DATES").Range("e12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d13") And c < Sheets("DATES").Range("e13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("d14") And c < Sheets("DATES").Range("e14") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("F9") And c <= Sheets("DATES").Range("G9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F10") And c < Sheets("DATES").Range("G10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F11") And c < Sheets("DATES").Range("G11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F12") And c < Sheets("DATES").Range("G12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F13") And c < Sheets("DATES").Range("G13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("F14") And c < Sheets("DATES").Range("G14") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("H9") And c <= Sheets("DATES").Range("I9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H10") And c < Sheets("DATES").Range("I10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H11") And c < Sheets("DATES").Range("I11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H12") And c < Sheets("DATES").Range("I12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H13") And c < Sheets("DATES").Range("I13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("H14") And c < Sheets("DATES").Range("I14") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("J9") And c <= Sheets("DATES").Range("K2")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J10") And c < Sheets("DATES").Range("K3") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J11") And c < Sheets("DATES").Range("K4") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J12") And c < Sheets("DATES").Range("K5") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J13") And c < Sheets("DATES").Range("K6") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("J14") And c < Sheets("DATES").Range("K7") Then
c.Interior.ColorIndex = 6

ElseIf c >= Sheets("DATES").Range("L9") And c <= Sheets("DATES").Range("M9")
Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L10") And c < Sheets("DATES").Range("M10") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L11") And c < Sheets("DATES").Range("M11") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L12") And c < Sheets("DATES").Range("M12") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L13") And c < Sheets("DATES").Range("M13") Then
c.Interior.ColorIndex = 6
ElseIf c >= Sheets("DATES").Range("L14") And c < Sheets("DATES").Range("M14") Then
c.Interior.ColorIndex = 6

Else
c.Interior.ColorIndex = 0

End If
Next
End Sub


merci de votre aide