Création d'un planning prévisionnel automatique

Bonjour à tous,

Déjà je remercie toute la communauté de ce forum, qui passera du temps sur mon problème.

J'explique un peu : je suis pas hyper compétent sur Excel et je n'utilise VBA que depuis quelques semaines (les tutos sur ce forum aident beaucoup).

Mais pour le taf je dois faire un outil financier sur Excel qui permettrait de créer un planning prévisionnel.

Pour le moment c'est sous forme de:

Tableau de date avec des colonnes : Période 1 Début --- Période 1 fin --- Période 2 début --- Période 2 fin ----- ...

Suivit d'un autre tableau mensuel : Janvier --- Fev --- Mars --- .... --- Decembre - Janvier --- Février --- ...

Ils entrent à la main les dates pour ensuite colorier les cases correspondantes encore à la main.

J'aimerais pouvoir faire une mise en forme automatique en fonction des dates référencées dans le premier tableau. J'ai bien essayé avec la mise en forme conditionnelle mais c'est pas très concluant.

Pour le VBA mon idée était d'extraire des cases B:I (cases de dates) grâce aux fonctions MOIS(date) / ANNEE(date)

le mois et l'année de la date sous format "JJ/MM/AAAA" pour donner par exemple :

31/08/2014 = MM+((AAAA-2014)*12) ---> 08+0 = 8

20/06/2016 = MM + ((AAAA-2014)*12) --> 06+24 = 30

Ce nombre donne le numéro de la case ou la mise en forme conditionnelle doit commencer dans le tableau mensuel.

Je pense que ça devrait marcher mais je suis pas certains de savoir l'écrire et surtout si c'est vraiment optimisé car pour actualiser tout le tableau il doit faire une boucle ligne à ligne.... De plus le changement de couleurs des cases serait alors uniquement effectué à l’exécution de la macro et non en temps réel. Mais en temps réel j'ai aucune idée comment procéder.

Voila j'espère avoir été clair et que vous pourrez m'aider dans la réalisation de ce projet !

Merci d'avance !!

Bonjour, une proposition à étudier

Bonjour !

Déjà merci pour ta réponse plus que rapide et très intéressante.

Je comprend le code en parti qui permet de zoner le tableau, de faire des intervalles de dates pour réintégrer dans le tableau de droite les 4 couleurs.

Après j'aimerais bien comprendre réellement comment ça marche (car bon faire du bête copié/collé c'est sympa mais c'est pas le but

Si tu pouvais commenter le code. Je te mets en commentaire ce que j'ai compris de lui (c'est tellement loin de ce que j'étais en train de faire de mon coté ............ j'ai du boulot...)

Option Base 1
Sub couleur()

'selectionne la feuille numéro 1 même si elle est renommée ? ou sélectionne la feuille actuellement sélectionnée  ?
a = Feuil1.[b9].CurrentRegion

'selectionne la zone à colorier
Feuil1.Range("J9:AS48").Interior.Pattern = xlNone

For i = 1 To UBound(a) 'je vois bien que tu défini un "i" (ligne) de 1 à "Ubound(a)" mais qu'est ce que c'est Ubound(a) ?
For j = 2 To 9 Step 2 'permet de définir J (colonne) de 2 à 9 soit de B à I avec un pas de 2 soit B -> D -> F....  
On Error Resume Next '<-- je connaissais pas, permet de faire une boucle malgré une erreur ?

If a(i, j) <> "" And a(i, j + 1) <> "" Then 'permet de dire que si la Date "n" et la date "n+1" différent de "rien". Par contre je comprend pas si "i"=1 to ubound(a) ici alors i=1 pour la première boucle et ça devrait sélectionner la ligne ('B1') et ('B2') non ? (c'est pas le cas le code marche mais je comprend pas) 
deb = DateDiff("m", "1/1/2014", a(i, j)) 'ca je comprend pas très bien... j'imagine que c'est pour les intervalles mais si tu peux m'éclairer...
duree = DateDiff("m", a(i, j), a(i, j + 1)) + 1 'de même

Feuil1.Cells(i + 8, deb + 10).Resize(, duree).Select 'je vois que tu sélectionnes la partie du tableau des dates mais si tu peux expliquer.
Selection.Interior.ColorIndex = Feuil1.Cells(1, (j / 2) + 4).Interior.ColorIndex 'ça sélectionne les couleurs en rangée 1, collonne (J/2)+4. Donc si on est dans la première séquence de date J=2 donc 2/2+4 = 5 donc cell('E1') si deuxième séquence de date J=4 donc 4/2+4=6 soit cell(F1) (super malin !....!)

End If
coul = coul + 1 'ca je comprend pas.... je connais pas fonction coul... 
Next
Next

End Sub

Merci beaucoup pour ton aide en tout cas !

Bonjour,

Je vous mets mon code avec mes commentaires et vous comparez avec ce que vous avez compris. Les langages sont très diverses entre chaque personne donc après lecture de mon commentaire, on refait un point sur ce qu'il faut eclaircir mais grosso modo vous avez prèsque tout juste.

Sub couleur()

a = Feuil1.[b9].CurrentRegion 'je place dans un tableau virtuel tout ce qu'il y a autour de b9 et qui est utilisé (pour comprendre comment currentregion fonctionne mettez vous sur une cellule et appuyer sur ctrl + *

Feuil1.Range("J9:AS48").Interior.Pattern = xlNone 'je selectionne une plage de cellule et je leur enlève toutes les couleurs (Feuil1 c'est le code name de la feuille donc celle-ci peut être renommée à souhait)

For i = 1 To UBound(a) 'Pour une valeur de i = 1 jusqu'à la dernière ligne du tableau virtuel (ubound(a))
For j = 2 To 9 Step 2 ' pour une valeur de j = 2 jusqu'à 9 qui se decalle de 2 en 2 (pour selectionner chaque case "début")
On Error Resume Next 'je l'ai mis je ne sais plus pour quoi mais je sais que ça permettait de ne pas avoir de bug avec datediff

If a(i, j) <> "" And a(i, j + 1) <> "" Then 'si 'il y a une date de début et de fin
deb = DateDiff("m", "1/1/2014", a(i, j)) 'je compte combien il y a de mois entre la date de début et le 1/1/2014)
duree = DateDiff("m", a(i, j), a(i, j + 1)) + 1 'je compte la durée en mois qu'il y a entre la date de début et de fin

Feuil1.Cells(i + 8, deb + 10).Resize(, duree).Select 'sur ma feuille et sur chaque ligne de chaque projet, je selectionne mon mois de départ (qui sera a x mois du 1/1/2014) que j'ai calculé dans la variable deb et j'agrandi ma selection (Resize) du nombre de mois que cela va durer (variable durée)
Selection.Interior.ColorIndex = Feuil1.Cells(1, (j / 2) + 4).Interior.ColorIndex ' a cette selection je lui met la couleur de la première cellule puis de la deuxième cellule lors de la deuxième boulce
End If
coul = coul + 1 'Euh...Je crois que cette variable est inutile ! Je l'avais utilisé pour faire je ne sais plus quel test...A supprimer.
Next
Next

End Sub

Pour la fonctionnement de datediff c'est assez simple,

En premier lieu on place l'indicateur que l'on veut mesurer les mois ou les années ou les jours... Donc là c'est "m" pour les mois , puis on place la date de début et de fin et datediff renvoi la difference...

Merci encore pour ta réponse !

Alors je comprend de mieux en mieux ce que tu fais (je suis loin de pouvoir l'écrire seul mais bon...)

Par contre j'ai un peu du mal avec la ligne ou tu places "un tableau virtuel" de tout ce qui y a autour de b9.

La machine comprend que ce tableau virtuel est dimensionné sur les plages (B9) to (AS48) ou (B9) to (I48) ? ou simplement que B9 sera le point de départ de la suite des ligne quand tu fais référence à "a" le tableau virtuel ?

Si je dois intégrer des colonnes/ligne à gauche/au dessus du tableau, qu'est ce que je dois changer dans le code.

J'imagine le Clean en ligne 2 (changer la plage du coup) et la définition du tableau virtuel (d’où ma question pour bien comprendre comment ça marche) mais le reste s'effectue directement dans ce tableau virtuel du coup y à pas autre chose à changer ?

Le tableau virtuel n'a plus comme coordonné A5,B6. Il a comme coordonée a(x,y) ou x = ligne virtuelle et y = colonne virtuelle.

Voir screenshot.

Pour le reste je répondrai plus tard car là je suis pris par le temps.

Si vous rajoutez ou supprimez des choses vous aurez la surprise en essayant le code.

Bonjour !

Effectivement, j'ai des surprises en essayant de réintégrer ton code dans un tableau qui transpose celui-ci non pas en colonne A mais en colonne AI.

Dans la première version modifié

a = Feuil1.[b9].CurrentRegion
Feuil1.Range("J9:AS168").Interior.Pattern = xlNone
'devient : 
a = Feuil1.[AI9].CurrentRegion
Feuil1.Range("AI9:BZ171").Interior.Pattern = xlNone

Mais ça marche juste pas ....

Ensuite j'ai essayé de bidouillé mais soit ça fait pas grand chose soit mes sélections devenaient bleu-ciel.

Bref je comprend pas trop pourquoi ça marche pas alors que j'ai globalement compris ce que fait chaque ligne...

Pour comprendre, sélectionnez avec votre souris la cellule AI9 puis appuyez sur ctrl * (la touche contrôle plus la touche étoile)

Cela va sélectionner un ensemble de cellule. C'est cet ensemble de cellule qui est intégré dans le tableau virtuel. Maintenant vous n'êtes obligé d’utiliser cette méthode (.currentregion), il existe d'autres façons pour définir une plage de cellule.

Mais je ne m’aventurerai pas à écrire un nouveau code où a expliquer quoi que ce soit compte-tenu qu'il existe une différence entre le fichier originale et le fichier de démo. C'est de mise ici mais moi, faire et défaire pour refaire ne m'enchante pas trop...

Ça marche, merci beaucoup de toute ton aide, de ta patience et de tes réponses !

Ne maitrisant pas bien .currentrégion je vais modifier un peu le code pour repartir sur des choses que je connais (surement nommer la plage via l'utilitaire de nom excel et sélectionner la plage nommé.

Merci beaucoup encore !

Ou alors fournir le fichier original (sans données sensibles) afin que la macro soit adaptée au bon fichier. Aux choix.