Optimisation de macro qui remplace MFC
Bonjour,
J’ai créé une macro qui remplace la MFC classique et qui fonctionne plutôt bien pour un planning que je suis en train d'élaborer. Le but est d’attribuer un format différent aux cellules selon leur valeur numérique (qui peut aller de 1 à 78).
Ces valeurs sont regroupées en 20 formats différents :
Sub colortest()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Set MyPlage = Range("CP9:YX328")
For Each cell In MyPlage
Select Case cell.Value
Case Is = "1", “21”, “41”, “61”
cell.Interior.Color = RGB(200, 0, 0)
cell.Font.Color = RGB(255, 229, 229)
Case Is = "2", “22”, “42”, “62”
cell.Interior.Color = RGB(204, 0, 102)
cell.Font.Color = RGB(255, 229, 229)
Case Is = "3", “23”, “43”, “63”
cell.Interior.Color = RGB(168, 42, 126)
cell.Font.Color = RGB(255, 229, 229)
Case Is = "4", “24”, “44”, “64”
cell.Interior.Color = RGB(134, 0, 134)
cell.Font.Color = RGB(242, 206, 231)
Case Is = "5", “25”, “45”, “65”
cell.Interior.Color = RGB(105, 0, 142)
cell.Font.Color = RGB(225, 204, 240)
Case Is = "6", “26”, “46”, “66”
cell.Interior.Color = RGB(88, 0, 176)
cell.Font.Color = RGB(236, 217, 255)
Case Is = "7", “27”, “47”, “67”
cell.Interior.Color = RGB(73, 0, 146)
cell.Font.Color = RGB(238, 221, 255)
Case Is = "8", “28”, “48”, “68”
cell.Interior.Color = RGB(34, 34, 138)
cell.Font.Color = RGB(189, 215, 238)
Case Is = "9", “29”, “49”, “69”
cell.Interior.Color = RGB(0, 87, 130)
cell.Font.Color = RGB(221, 235, 247)
Case Is = "10", “30”, “50”, “70”
cell.Interior.Color = RGB(0, 148, 222)
cell.Font.Color = RGB(217, 242, 255)
Case Is = "11", “31”, “51”, “71”
cell.Interior.Color = RGB(0, 162, 200)
cell.Font.Color = RGB(229, 248, 255)
Case Is = "12", “32”, “52”, “72”
cell.Interior.Color = RGB(0, 132, 146)
cell.Font.Color = RGB(225, 252, 255)
Case Is = "13", “33”, “53”, “73”
cell.Interior.Color = RGB(0, 102, 102)
cell.Font.Color = RGB(226, 239, 218)
Case Is = "14", “34”, “54”, “74”
cell.Interior.Color = RGB(0, 104, 52)
cell.Font.Color = RGB(226, 239, 218)
Case Is = "15", “35”, “55”, “75”
cell.Interior.Color = RGB(105, 142, 0)
cell.Font.Color = RGB(244, 255, 213)
Case Is = "16", “36”, “56”, “76”
cell.Interior.Color = RGB(214, 173, 0)
cell.Font.Color = RGB(255, 254, 197)
Case Is = "17", “37”, “57”, “77”
cell.Interior.Color = RGB(248, 165, 0)
cell.Font.Color = RGB(255, 248, 229)
Case Is = "18", “38”, “58”, “78”
cell.Interior.Color = RGB(248, 136, 12)
cell.Font.Color = RGB(255, 239, 231)
Case Is = "19", “39”, “59”
cell.Interior.Color = RGB(238, 96, 0)
cell.Font.Color = RGB(255, 218, 193)
Case Is = "20", “40”, “60”
cell.Interior.Color = RGB(204, 102, 0)
cell.Font.Color = RGB(255, 226, 197)
End Select
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
Application.CutCopyMode = False
End Sub
Néanmoins, je rencontre quelques petites difficultés :
1. Comme vous pouvez le constater, j’ai essayé d’optimiser ma macro avec Application.ScreenUpdating, Application.Calculation, Application.EnableEvents et ActiveSheet.DisplayPageBreaks au début et à la fin, mais elle reste toujours beaucoup trop lente. Comment je peux l’optimiser pour faire en sorte que chaque cellule change de façon instantanée selon sa valeur, comme quand on met une MFC quelconque (je ne veux surtout pas utiliser la MFC vu le nombre de conditions) ?
2. J’ai ajouté également une deuxième macro qui déclenche la première quand il y a un changement de cellule :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range
Set iSect = Intersect(Target, [J1])
If not iSect Is Nothing Then call mamacro
End Sub
Cependant, quand j’efface une valeur sur une cellule quelconque, le format de cette valeur reste là (en l'occurrence la couleur de la cellule) . Je souhaite donc faire en sorte que quand j’efface la valeur, le format de celle-ci s’efface aussi tout simplement, c’est-à-dire que la cellule retrouve son format initial, comme quand on met une MFC conditionnelle quelconque.
J'ai fait beaucoup recherches sur Internet, j'ai lu tous les forums, j'ai passé de nombreuses heures en essayant plusieurs codes, mais je n'ai pas encore réussi à trouver une solution à mon problème, hélas...
Est-ce quelqu'un pourrait m'aider s'il vous plaît ?
Merci beaucoup !!!
Monica
Bonjour,
Question bête, mais pourquoi se compliquer la vie à reproduire le travail d'une MFC plutôt que d'utiliser directement celle-ci ?
Autre remarque sur la forme : ne pas se priver d'utiliser le bouton </> lorsque l'on post un script VBA sur le forum...
Bonjour Pedro,
J'avais au début une MFC. Mais elle faisait énormément de bugs sur Excel vu le nombre de conditions.
En plus, la MFC se réproduit automatiquement à chaque fois que je fais un copier/coller (même avec un collage spécial). Du coup, ça fait que j'avais des milliers de conditions qui polluaient mon document et qui se multipliaient à l'infini ! Je n'ai plus ce problème avec VBA...
bonjour
salut au passage Pedro
as-tu lu mon message perso ?
même avis que Pedro
1/
pourquoi faire des copier/coller ?
cette opération se fait lors de la mise au point d'un classeur. Par la suite, à l'utilisation, on n'en fait plus.
2/
collage spécial valeur ne reproduit pas les MFC.
amitiés
1/ On a besoin de faire des copier/coller tout le temps dans ce planning, car ça bouge tout le temps.
2/ J'ai fait plusieurs tests en utilisant le collage spécial, et les MFC se reproduisaient quand même... D'où l'idée de les remplacer par une macro... Enfin... C'est vraiment impossible d'optimiser celle que j'ai créée ?
Bonjour m_onik, bonjour le forum,
Pour la lenteur du code, pour le moment, je n'ai pas d'idées...
Mais pour que la cellule revienne à la couleur initiale si la valeur de celle-ci est effacée, il faut rajouté dans ton code une condition :
If cell.Value <> "" Then
--------------------------------
-------TON CODE-------
--------------------------------
Else
With cell.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
NextCordialement,
IB
Peux-tu nous envoyer un fichier exemple ? Je crois qu'on manque d'éléments avec ton seul code VBA. On ne sait pas non plus quelle est le besoin réel dernière ce script : que cherches tu à mettre en valeur ou à distinguer ?
Je pense que n'arrivant pas à faire ce que tu souhaites, tu t'es dit que VBA étais la solution et donc que ton problème concerne le code VBA. Je ne suis pas de cet avis, et je pense qu'il est possible de faire autrement (et peut-être que j'ai tort).
re à tous
comme mis dans mon MP auquel tu as répondu :
1- revenir aux MFC
2- se limiter à 7 ou 8 couleurs pour rester lisible
3- collage spécial valeur ne recopie pas les MFC
4- tu as eu des soucis de MFC, mais tu n'as pas cherché d'aide. tu préfères abandonner
5- tu as des soucis de VBA et tu cherches de l'aide et en veux pas abandonner
4 et 5 sont étranges
Bonjour IronBoule,
Merci pour ta réponse.
J'ai essayé ton code, mais ça ne marche pas...
Je fais peut-être une erreur quelque part :/
Le but est tout simplement de mettre en relief les cellules qui ont des valeurs numérotées.
Chaque valeur de 1 à 78 correspond à un produit.
Je t'envoie ci-joint le fichier.
Merci de ton aide !
Re bonjour jmd,
En fait, la MFC m'a trop écœurée. En plus, le collage spécial implique de faire trop de clics (et les raccourcis respectifs restent quand même trop longs).
Pour y remédier, j'ai essayé de faire ensuite une macro qui ne reproduise pas la MFC conditionnelle à chaque fois que je fais un copier/coller, et ça marchait plutôt bien... Le problème est qu'elle enlevait aussi toutes mes cellules fusionnées, c'est-à-dire qu'elle modifiait toute la mise en page de mon document... C'est pourquoi j'ai essayé de faire la même chose, mais autrement...
Malheureusement, on doit faire beaucoup mais beaucoup de copier/coller sur le document en question. Et la quantité d'éléments à mettre en avant sont très nombreux aussi, soit 78 valeurs à mettre en avant (juste 8 formats ne me semblent pas suffisants pour avoir une vue d'ensemble de toutes ces données, ça fait un peu mal aux yeux) :/
re
1/
ne JAMAIS fusionner de cellules dans Excel, source de problèmes avec toutes sortes de fonctions et avec VBA aussi.
pour faire "joli" il y a "centrer sur colonnes"
2/
en conception, on fait des copier/coller au clavier/souris
en utilisation, on n'en fait pas : faire autrement
Bonjour, Salut à tous !
Je suis effaré !
Certes, le découpage en 20 catégories oblige à définir 20 MFC, mais chacune sera dotée d'une formule tout à fait simple !
=MOD(A1;20)=xA1 est la cellule gauche de la plage d'application,
x sera un nombre de 0 à 19, variant pour chacune des MFC.
Et si on veut le faire par macro, on commence par définir un tableau d'indices 0 à 19 des couleurs à affecter, et une boucle de 0 à 19 permettra de créer les 20 MFC avec un code des plus réduits !
Car si on peut justifier d'une macro en raison du caractère itératif de l'opération, l'abandon de la MFC ne se justifie en rien !
Cordialement.
Bonjour MFerrand,
Désolée, je débute en programmation de macros, c'est pourquoi je suis venue chercher de l'aide, des conseils et des avis bienveillants.
Euh bien, j'ai enlevé les guillemets qui effectivement n'étaient pas utiles. La macro reste quand même trop lente.
Et à vrai dire, je ne comprends pas trop votre formule.
Pourquoi je ne souhaite passer par la MFC ? Tout simplement pour éviter que la MFC se reproduise à l'infini à chaque copié/collé.
Certes, il existe le collage spécial, mais il faut faire beaucoup de clics et les raccourcis sont longs. Et la macro que j'ai trouvée pour automatiser cette action modifie toute la mise en page de mon document.
Re,
Macro pour insérer MFC :
Sub ColorMFC()
Dim clr(19, 1) As Long, i%
clr(0, 0) = RGB(204, 102, 0): clr(0, 1) = RGB(255, 226, 197)
clr(1, 0) = RGB(200, 0, 0): clr(1, 1) = RGB(255, 229, 229)
clr(2, 0) = RGB(204, 0, 102): clr(2, 1) = RGB(255, 229, 229)
clr(3, 0) = RGB(168, 42, 126): clr(3, 1) = RGB(255, 229, 229)
clr(4, 0) = RGB(134, 0, 134): clr(4, 1) = RGB(242, 206, 231)
clr(5, 0) = RGB(105, 0, 142): clr(5, 1) = RGB(225, 202, 240)
clr(6, 0) = RGB(88, 0, 176): clr(6, 1) = RGB(236, 217, 255)
clr(7, 0) = RGB(73, 0, 146): clr(7, 1) = RGB(238, 221, 255)
clr(8, 0) = RGB(34, 34, 138): clr(8, 1) = RGB(189, 215, 238)
clr(9, 0) = RGB(0, 87, 130): clr(9, 1) = RGB(221, 235, 247)
clr(10, 0) = RGB(0, 148, 222): clr(10, 1) = RGB(217, 242, 255)
clr(11, 0) = RGB(0, 162, 200): clr(11, 1) = RGB(229, 248, 255)
clr(12, 0) = RGB(0, 132, 146): clr(12, 1) = RGB(225, 252, 255)
clr(13, 0) = RGB(0, 102, 102): clr(13, 1) = RGB(226, 239, 218)
clr(14, 0) = RGB(0, 104, 52): clr(14, 1) = RGB(226, 239, 218)
clr(15, 0) = RGB(105, 142, 0): clr(15, 1) = RGB(244, 255, 213)
clr(16, 0) = RGB(214, 173, 0): clr(16, 1) = RGB(255, 254, 197)
clr(17, 0) = RGB(248, 165, 0): clr(17, 1) = RGB(255, 248, 229)
clr(18, 0) = RGB(248, 136, 12): clr(18, 1) = RGB(255, 239, 231)
clr(19, 0) = RGB(238, 96, 0): clr(19, 1) = RGB(255, 218, 196)
With ActiveSheet.Range("CP9:YX328").FormatConditions
.Delete
For i = 0 To 19
With .Add(xlExpression, , "=ET(ESTNUM(CP9);CP9>0;MOD(CP9;20)=" & i & ")")
.Interior.Color = clr(i, 0)
.Font.Color = clr(i, 1)
End With
Next i
End With
End SubLa formule MFC sera peut-être à revoir en fonction du contenu éventuel des cellules... On y a prévu que la MFC doit s'appliquer si la valeur est numérique, supérieure à 0 et le modulo de 20 égal à une valeur de 0 à 19 (selon cas).
Non testé évidemment (j'espère sans erreur de frappe...
Cordialement.
Re à nouveau ,
Pourquoi je ne souhaite passer par la MFC ? Tout simplement pour éviter que la MFC se reproduise à l'infini à chaque copié/collé.
Rien compris à ton histoire de collage, il faudrait préciser dans quel contexte cela se produit.
Woahhhhhh ! C'est exactement ce que je cherchais !!!! Ton code est juste parfait !!!
Tu as rendu heureux quelqu'un aujourd'hui !!!!
Merci infiniment MFerrand !!!!!
Re à nouveau ,
Pourquoi je ne souhaite passer par la MFC ? Tout simplement pour éviter que la MFC se reproduise à l'infini à chaque copié/collé.
Rien compris à ton histoire de collage, il faudrait préciser dans quel contexte cela se produit.
En fait, quand on fait un copier/coller à partir d"une cellule déjà affectée par la MFC, toutes ses règles de MFC se copient également.
Certes, on peut éviter ça en faisant > collage spécial > "copier les formats", mais je trouve que c'est trop manuel, trop ennuyeux et trop long, surtout quand on doit copier/coller une quantité colossale de données sur un même document. Je ne sais pas si je me fais comprendre...
Enfin, j'ai trouvé mon bonheur grâce à ton super code qui fonctionne juste trop bien !
Merci beaucoup !!!!!!
Et à vrai dire, je ne comprends pas trop votre formule.
Tes mises en formes se répètes à intervalle de 20, plutôt que de préciser à chaque fois 0, 20, 40, 80... MFerrand (salut, au passage !) propose une formule qui en tient compte. MOD (pour MODULO) donne le reste d'une division entière, par exemple 0 pour 0, 20, 40 divisés par 20... ou 1 pour 1, 21, 41...
Aussi, la solution proposée ne résout pas le problème de fond, à savoir que tu copies apparemment sans cesse des quantités importantes de données.