Suivi des modifications dans les cellules de formule - Conseils Excel

Suivi des modifications dans les cellules de formule Excel. Pouvez-vous montrer les éléments qui viennent d'être modifiés suite à la modification de certaines cellules d'entrée?

Regarder la vidéo

Suivre les modifications dans Excel est un peu bizarre.
L'objectif est de suivre les modifications apportées aux cellules de formule dans Excel.
Enregistrer sous pour enregistrer le classeur au format XLSM.
Modifier la sécurité des macros.
Enregistrez une macro pour déterminer le code pour configurer la mise en forme conditionnelle pour les nombres non égaux à 2.
Choisissez le formatage souhaité.
Enregistrez une autre macro pour savoir comment supprimer CF de la feuille de calcul.
Dans la macro, ajoutez une boucle pour chaque feuille de calcul.
Ajoutez une instruction IF pour l'empêcher de s'exécuter sur Title.
Ajoutez une boucle pour vérifier chaque cellule de formule.
Ajoutez la mise en forme conditionnelle pour voir si la valeur de la cellule au moment de l'exécution de la macro.
Revenez à Excel.
Ajoutez une forme. Attribuez la macro à la forme.
Cliquez sur la forme pour exécuter la macro.
Astuce bonus: faites glisser un module VBA vers un nouveau classeur.

Transcription vidéo

Apprendre Excel à partir du podcast, épisode 2059: Excel Suivre les modifications (dans les résultats de formule)

Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui envoyée de Montréal sur les changements de piste. Suivez les modifications, d'accord. Alors, voici ce que nous avons. Nous avons 4 cellules d'entrée et tout un tas de cellules de formule qui reposent sur ces cellules d'entrée. Et si je devais activer, je retournerai à l'onglet Révision, activer les modifications de surbrillance, suivre les modifications lors de l'édition, cliquer sur OK, d'accord. Et ils m'ont prévenu qu'ils devaient enregistrer le classeur et que les macros ne pouvaient pas être utilisées dans les classeurs partagés. Tu le sais? C'est le problème lorsque vous suivez les modifications, ils partagent le classeur et il y a tout un tas de choses qui ne peuvent pas se produire dans les classeurs partagés, vous savez, comme les macros et tout un tas d'autres choses. Mais regardons simplement comment le suivi des modifications fonctionne dans Excel aujourd'hui.

Prenons ce 2 et changeons de 2 à 22, et prenons ce 4 et changeons-le de 4 à 44. D'accord, et vous voyez, ce qu'ils ont noté dans le suivi des changements, c'est que ces deux cellules ont changé, d'accord, ces triangles violets sont la piste réelle change. Tous ces trucs rouges, ça n'arrive pas mais je viens d'illustrer que tous ces globules rouges changent et que le suivi des changements ne dit rien sur ces changements, d'accord? Donc, cela dit simplement, ces deux cellules ont été modifiées, mais toutes ces autres cellules ont également été modifiées. Et donc, la question de Montréal est la suivante: y a-t-il un moyen pour que le suivi des changements nous montre réellement tout ce qui change, pas seulement ces cellules d'entrée qui ont changé?

Très bien, donc, la première chose à faire est de désactiver le suivi des modifications intégré à Excel. Et puis, y a-t-il un moyen que nous pouvons obtenir - nous pouvons construire notre propre système de suivi des changements qui nous permettra de voir toutes les cellules de formule qui ont changé? D'accord, donc l'étape 1 et cette étape est l'étape la plus importante, ne l'ignorez pas. Regardez votre fichier, votre fichier s'appelle quelque chose XLSX, vous devez enregistrer ceci: Fichier, Enregistrer sous, En tant que classeur activé par macro, ou rien de tout cela ne fonctionnera. Vous devez cliquer avec le bouton droit de la souris, personnaliser le ruban, activer Developer, une fois que vous arrivez à Developer, aller à Macro Security, changer à partir de ce paramètre - celui qui dit que nous n'allons pas laisser les macros s'exécuter ou même pas le dire vous qu'ils sont là pour ce paramètre. Vous devez suivre ces deux étapes. J'ai déjà fait ces deux étapes. Je vis tous les jours avec ces deux étapes.Déjà corrigé, mais si vous êtes nouveau dans les macros, c'est nouveau pour vous. Et puis, nous devons déterminer le type de formatage souhaité. Très bien, donc je vais juste choisir quelques cellules ici, je vais enregistrer une macro qui s'appelle HowToCFRed, je ne vais pas attribuer une touche de raccourci car cela ne fonctionnera plus jamais. J'enregistre simplement du code pour comprendre comment fonctionne le formatage conditionnel. Et nous allons entrer dans Accueil, mise en forme conditionnelle, mettre en évidence les cellules qui ne sont pas égales à - Donc, plus de règles, format de cellules différent de - Vous voyez? Ce n'est pas dans la liste déroulante d'origine, mais si vous venez ici, pas égal à 2, puis choisissez le format. C'est la partie importante. Je vais donc choisir un fond rouge. Vous choisissez la couleur que vous voulez ici, d'accord? Allez même à Plus de couleurs, choisissez un autre rouge,allez dans Custom, choisissez un autre rouge, d'accord? C'est la beauté de l'enregistreur de macros, ils vont nous fournir du rouge parfait pour vous ou du bleu ou tout ce que vous voulez. Très bien, cliquez sur OK. Et puis, nous allons arrêter l'enregistrement, d'accord. Encore une fois, le but de tout cela est simplement de voir quel est le code pour les formats conditionnels.

Je vais à Macros, Comment mettre en forme conditionnelle rouge et modifier. Très bien, voici donc les parties importantes de ce code. Je peux voir qu'ils ajoutent un format conditionnel en utilisant le xlNotEqual et nous le citons dur pour ne pas être égal à 2. Et puis nous changeons l'intérieur de la cellule en cette couleur.

Très bien, je dois également comprendre comment supprimer toute mise en forme conditionnelle sur la feuille. Donc, de retour à Excel, enregistrez une autre macro, comment supprimer tous les conditionnels, OK. Allez ici dans l'onglet Accueil, allez dans Mise en forme conditionnelle, Effacer la règle de la feuille entière, Arrêter l'enregistrement et nous allons examiner ce code. Super, c'est une macro sur une ligne. Et j'aime même ici que la façon dont ils le font pour la feuille entière est de se référer simplement aux cellules. Donc, en d'autres termes, toutes les cellules de la feuille active.

Maintenant, je dois rendre cette macro, la macro enregistrée, un peu plus générique. Et j'ai écrit beaucoup de livres sur la façon de faire VBA dans Excel et j'ai fait des vidéos sur la façon de faire VBA dans Excel, et voici la chose simple: vous devez être capable d'enregistrer une macro comme celle-ci, mais ensuite, ajoutez environ cinq ou six lignes afin de pouvoir rendre la macro suffisamment générique.

Et je vais parler de ces lignes, d'accord. Donc, la première chose que je veux faire est de dire que je veux parcourir le classeur actif, parcourir toutes les feuilles de calcul. Donc, pour chaque feuille de calcul, WS est la variable objet, je vais parcourir toutes les feuilles de calcul. Et la personne de Montréal a dit: «Hé, il y a une feuille sur laquelle je ne veux pas que cela se produise.» Donc, si WS.Name, avec le nom de point de la feuille de calcul, n'est pas égal à Title, nous allons faire le code dans la macro. Voici le nom de la feuille: .Cells.FormatConditions.Delete. Donc, nous allons parcourir chaque individu de la feuille à l'exception du titre et supprimer toutes les conditions de format, puis nous allons parcourir chaque cellule de la feuille mais pas toutes les cellules, juste les cellules qui ont des formules . Si ça n'a pas de formule alors je n'en ai past besoin de le formater car il ne va pas changer. Cell.FormatConditions.Add, c'est directement à partir de la macro bien que la macro enregistrée dise Sélection - je ne veux pas avoir à la sélectionner donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser le xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS. ne va pas changer. Cell.FormatConditions.Add, c'est directement à partir de la macro bien que la macro enregistrée dise Sélection - je ne veux pas avoir à la sélectionner donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.ne va pas changer. Cell.FormatConditions.Add, c'est directement à partir de la macro bien que la macro enregistrée dise Sélection - je ne veux pas avoir à la sélectionner donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.c'est directement à partir de la macro bien que la macro enregistrée dise Sélection - je ne veux pas avoir à la sélectionner, donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.c'est directement à partir de la macro bien que la macro enregistrée dise Sélection - je ne veux pas avoir à la sélectionner, donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser le xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.Je ne veux pas avoir à le sélectionner donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser le xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.Je ne veux pas avoir à le sélectionner, donc je vais juste dire Cell, c'est chaque cellule individuelle. Nous allons utiliser le xlNotEqual et au lieu de Formula: = ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.= ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.= ”=” 2 qui est ce que le code enregistré a fait juste là, j'ai concaténé tout ce qui se trouve dans cette cellule. Vérifiez donc si ce n'est pas égal à la valeur actuelle. Donc, si la cellule a actuellement 2, nous disons pas égal à 2. Si la cellule a actuellement 16,5, nous disons pas égal à 16,5. Et puis le reste est juste une macro enregistrée, une macro enregistrée, une macro enregistrée, une macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.macro enregistrée, macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.macro enregistrée, macro enregistrée. Tout cela provient d'une macro enregistrée. Terminez ce If avec un End If. Terminez ceci pour avec un prochain WS.

Très bien, j'ai donc une macro appelée ApplyCF. Revenez dans Excel, ajoutez une forme. Facile d'avoir une forme ici: Insérer, je choisis toujours un rectangle arrondi, tapez Reset To Current Values. Nous appliquerons Home, le centre et le centre pour le rendre un peu plus grand. J'adore la lueur. Je suppose que vous pensez que c'est idiot de voir que ce n'est pas là, la lueur, le paramètre que j'aime n'est pas là, alors je vais toujours à la mise en page et aux effets et je choisis ce second. Et puis quand je reviens au format, je peux en choisir un qui a en fait un peu d'éclat. Pour moi, je pense que ça a l'air cool, je pense que ça vaut le coup. Cliquez avec le bouton droit de la souris, attribuez une macro et dites ApplyCF, cliquez sur OK. D'accord, et ce que cela fera, c'est que lorsque je clique dessus, il parcourra toutes ces feuilles, trouvera toutes les cellules de formule et configurera une mise en forme conditionnelle qui dit: Si ces cellules ne sont pas égales à 7,changer la couleur, d'accord? C'est tout. C'est si vite que ça s'est passé si vite. BAM! C'est fait. Et maintenant, regardez si je change celui-ci en 11, toutes ces cellules viennent de changer. Maintenant, si ça revient au 1, ahh, les couleurs ont changé. Donc, quelle que soit la valeur, quand nous changeons - si je change cette cellule, toutes ces cellules changent. Si je change cette cellule, toutes ces cellules changent. Si je change cette cellule, toutes ces cellules changent.toutes ces cellules changent.toutes ces cellules changent.

Alright, now this is the new normal. Now from here, I want to track again. So I Reset To Current Values and then if I change this one to a 3, those sales change. Oh, by the way, these cells back here and these other sheets also changed in response to this. Track changes in Excel as it exists? Yeah, it's really lame. It doesn't show you the things that changed and having to show the workbook is a horrible, horrible thing. But with this simple, simple little macro, it works.

Alright now, the question said, alright, so if this is working how do I now get this to work in my other workbook? So I have some other workbook and I want to copy this over. Alright, so this is a great little bonus tip here. I'll create a brand new workbook and we'll put some stuff in here and I'll have a couple of formulas, and put a cell up there, alright. So we changed that cell, those 4 cells are all formula cells. Now if I want this workbook, Book2, to also have the code from podcast 2059, well I could retype it all again but that would be silly. So we come here to the Developer tab, and go to Visual Basic. And I want to make sure that I can see Book2 and then I can see Podcast 2059. I simply take that module and drag it and drop it on Book2, right there. And now, that code is also in Book2. Coming back to Book2, just add a shape, right click, Assign Macro, click OK. Alright, it works. And then 3, see, we've now applied that setting to this workbook.

Great question. Great question sent in from Montreal. And in this case, great question that my initial reaction is, well yeah, you're right. Track changes is horrible in Excel. And I wonder if I could create something that would actually track the changes. What are the downsides here and I'm sure I'm going to hear about this in the YouTube comments. If you had 10,000 formula cells, well now, all of these conditional formattings are going to be volatile, the things going to slow down, too many Excel format errors. Yeah, I can see all that but, you know, for a nice small workbook 5,6, 7 sheets, maybe 50 rows per sheet, I would think that this has some chance- some chance are working.

Okay, episode recap: Track changes in Excel, it's a little bizarre especially because they share the workbook. Our goal is to track what formula cells in Excel change. You have to save the workbook as XLSM, change your Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers and not equal to 2, that’s just to figure out what red you want to use. Choose the formatting you want, Record another macro to learn how to remove conditional formatting from the worksheet. And then, to that macro that we recorded the first one, add a loop for each worksheet, an IF statement to prevent it from running on the title sheet, then a loop to check each formula cell. Add conditional formatting to each cell that says, if this CELL.VALUE is not equal to the value at the time that it ran, then we're going to highlight the things. Go back to Excel, assign a shape, add a shape, assign a macro to the shape, click the shape to run the macro. And I also showed you the bonus tip: dragging a VBA module to a new workbook.

Eh bien, je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.

Télécharger un fichier

Téléchargez l'exemple de fichier ici: Podcast2059.xlsm