Houd wijzigingen in formulecellen bij - Excel-tips

Houd wijzigingen bij in Excel-formulecellen. Kunt u laten zien welke items zojuist zijn gewijzigd als gevolg van het wijzigen van bepaalde invoercellen?

Bekijk video

Wijzigingen bijhouden in Excel is een beetje bizar.
Het doel is om bij te houden welke formulecellen in Excel veranderen.
Opslaan als om de werkmap op te slaan als XLSM.
Wijzig macrobeveiliging.
Neem een macro op om de code te achterhalen om voorwaardelijke opmaak in te stellen voor getallen die niet gelijk zijn aan 2.
Kies de opmaak die u wilt.
Neem een andere macro op om te leren hoe u CF uit het werkblad verwijdert.
Voeg in de macro een lus toe voor elk werkblad.
Voeg een IF-instructie toe om te voorkomen dat deze op Title wordt uitgevoerd.
Voeg een lus toe om elke formulecel te controleren.
Voeg voorwaardelijke opmaak toe om te zien of de celwaarde tijdens het uitvoeren van een macro wordt uitgevoerd.
Ga terug naar Excel.
Voeg een vorm toe. Wijs de macro toe aan de vorm.
Klik op de vorm om de macro uit te voeren.
Bonustip: een VBA-module naar een nieuwe werkmap slepen.

Videotranscriptie

Leer Excel van Podcast, aflevering 2059: Excel-wijzigingen bijhouden (in formule-resultaten)

Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag vanuit Montreal over trackwijzigingen. Houd veranderingen bij, oké. Dus hier is wat we hebben. We hebben 4 invoercellen en een hele reeks formulecellen die op deze invoercellen vertrouwen. En als ik het zou inschakelen, ga ik terug naar het tabblad Controleren, schakel ik Wijzigingen markeren in, houd de wijzigingen bij tijdens het bewerken, klik op OK, oké. En ze waarschuwden me dat ze de werkmap moeten opslaan en dat macro's niet kunnen worden gebruikt in gedeelde werkmappen. Dat weet je? Dit is het probleem wanneer u wijzigingen bijhoudt, ze de werkmap delen en er zijn een heleboel dingen die niet kunnen gebeuren in gedeelde werkmappen, zoals macro's en een heleboel andere dingen. Maar laten we eens kijken hoe het bijhouden van wijzigingen vandaag in Excel werkt.

Laten we deze 2 nemen en wijzigen van 2 in 22, en deze 4 veranderen van 4 in 44. Oké, en zie je, wat ze hebben opgemerkt in trackwijzigingen is dat deze twee cellen zijn veranderd, oké, die paarse driehoeken zijn het daadwerkelijke nummer verandert. Al deze rode dingen, dat gebeurt niet, maar ik heb zojuist geïllustreerd dat al deze rode bloedcellen aan het veranderen zijn en veranderingen bijhouden zegt niets over deze veranderingen, oké? Dus, er staat alleen maar dat deze twee cellen zijn veranderd, maar al deze andere cellen zijn ook veranderd. En dus is de vraag uit Montreal: is er een manier om trackwijzigingen ons werkelijk alles te laten zien dat verandert, niet alleen deze invoercellen zijn veranderd?

Oké, dus het eerste wat we moeten doen, is de in Excel ingebouwde Track Changes uitschakelen. En dan, is er een manier waarop we kunnen … we kunnen ons eigen trackwijzigingssysteem bouwen waarmee we alle formulecellen kunnen zien die zijn veranderd? Oké, dus stap 1 en deze stap is de belangrijkste stap, sla dit niet over. Kijk naar je bestand, je bestand heet iets XLSX, je moet dit opslaan: Bestand, Opslaan als, Als een macro-geactiveerde werkmap, anders werkt niets van dit alles. Je moet met de rechtermuisknop klikken, het lint aanpassen, de ontwikkelaar inschakelen, zodra je bij de ontwikkelaar bent, ga je naar macrobeveiliging, verander je van deze instelling - degene die zegt dat we macro's niet laten draaien of zelfs niet vertellen u dat ze daar zijn in deze setting. Je moet deze twee stappen doen. Ik heb die twee stappen al gedaan. Ik leef elke dag met die twee stappen.Al opgelost, maar als macro's nieuw voor u zijn, is dit nieuw voor u. En dan moeten we uitzoeken wat voor soort opmaak u wilt. Oké, dus ik ga hier gewoon wat cellen kiezen, ik ga een macro opnemen die HowToCFRed heet, ik ga niet toewijzen aan een sneltoets omdat deze nooit meer zal worden uitgevoerd. Ik neem gewoon code op om erachter te komen hoe voorwaardelijke opmaak werkt. En we gaan naar Home, Voorwaardelijke opmaak, Cellen markeren die niet gelijk zijn aan - Dus, meer regels, Cellen opmaken die niet gelijk zijn aan - Zie je dat? Het staat niet in de oorspronkelijke vervolgkeuzelijst, maar als je hier binnenkomt, niet gelijk aan 2, en kies dan het formaat. Dit is het belangrijkste deel. Dus ik ga een rode achtergrond kiezen. Jij kiest hier welke kleur je wilt, oké? Ga zelfs naar Meer kleuren, kies een ander rood,ga naar Aangepast, kies een ander rood, oké? Dat is het mooie van de Macro Recorder, ze gaan ons perfect rood voor je geven of blauw of wat je maar wilt. Oké, klik op OK. En dan stoppen we met opnemen, oké. Nogmaals, het hele punt hiervan is om te zien wat de code is voor voorwaardelijke formaten.

Ik ga naar macro's, voorwaardelijk rood opmaken en bewerken. Oké, dus hier zijn de belangrijke delen van deze code. Ik kan zien dat ze een voorwaardelijk formaat toevoegen met behulp van xlNotEqual en we citeren het moeilijk om niet gelijk te zijn aan 2. En dan veranderen we het interieur van de cel in die kleur.

Oké, ik moet ook uitzoeken hoe ik alle voorwaardelijke opmaak op het blad kan verwijderen. Dus, terug naar Excel, neem nog een macro op, Hoe alle voorwaardelijk te verwijderen, OK. Kom hier naar het tabblad Home, ga naar Conditionele opmaak, Wis regel uit het hele blad, Stop opname en we gaan naar die code kijken. Geweldig, het is een macro met één regel. En ik vind het hier zelfs leuk dat de manier waarop ze het voor het hele blad doen, is dat het alleen naar cellen verwijst. Met andere woorden, alle cellen op het actieve blad.

Nu moet ik deze macro, de opgenomen macro, een beetje algemener maken. En ik heb veel boeken geschreven over hoe je VBA in Excel moet doen en ik heb video's gemaakt over hoe je VBA in Excel moet doen, en hier is het simpele: je moet een macro als deze kunnen opnemen, maar voeg dan toe ongeveer vijf of zes regels om de macro generiek genoeg te kunnen maken.

En ik ga het over die regels hebben, oké. Dus het eerste dat ik wil doen, is dat ik wil zeggen, ik wil het actieve werkboek doornemen, alle werkbladen doornemen. Dus voor elk werkblad is WS de objectvariabele, ik zal alle werkbladen doornemen. En de persoon uit Montreal zei: "Hé, er is één blad waarop ik niet wil dat dit gebeurt." Dus als de WS.Name, met de werkbladpuntnaam, niet gelijk is aan Titel, dan gaan we de code in de macro doen. Hier is de bladnaam: .Cells.FormatConditions.Delete. Dus we gaan door elk afzonderlijk van het blad behalve de titel en verwijderen alle opmaakvoorwaarden, dan gaan we door elke cel in het blad, maar niet alle cellen, alleen de cellen met formules . Als het geen formule heeft, dan doe ik het niet 'Het hoeft niet te worden geformatteerd omdat het niet zal veranderen. Cell.FormatConditions.Add, dit komt rechtstreeks uit de macro, hoewel de opgenomen macro Selectie zei - ik wil het niet hoeven te selecteren, dus ik zeg gewoon Cell, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS. gaat niet veranderen. Cell.FormatConditions.Add, dit komt rechtstreeks uit de macro, hoewel de opgenomen macro Selectie zei - ik wil het niet hoeven te selecteren, dus ik zeg gewoon Cell, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.gaat niet veranderen. Cell.FormatConditions.Add, dit komt rechtstreeks uit de macro, hoewel de opgenomen macro Selectie zei - ik wil het niet hoeven te selecteren, dus ik zeg gewoon Cell, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.dit komt rechtstreeks uit de macro, hoewel de opgenomen macro Selectie zei - ik wil het niet hoeven te selecteren, dus ik zeg gewoon Cel, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.dit komt rechtstreeks uit de macro, hoewel de opgenomen macro Selectie zei - ik wil het niet hoeven te selecteren, dus ik zeg gewoon Cel, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.Ik wil het niet moeten selecteren, dus ik zeg gewoon Cell, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.Ik wil het niet selecteren, dus ik zeg gewoon Cell, dat is elke individuele cel. We gaan de xlNotEqual gebruiken en in plaats van Formula: = ”=” 2, wat de opgenomen code daar deed, heb ik alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.= ”=” 2 wat de opgenomen code daar deed, ik heb alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.= ”=” 2 wat de opgenomen code daar deed, ik heb alles in die cel samengevoegd. Dus kijk of deze niet gelijk is aan de huidige waarde. Dus als de cel momenteel 2 heeft, zeggen we niet gelijk aan 2. Als de cel momenteel 16,5 heeft, zeggen we niet gelijk aan 16,5. En dan is de rest gewoon gewoon opgenomen macro, opgenomen macro, opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.opgenomen macro, opgenomen macro. Dat alles is van een opgenomen macro. Beëindig deze If met een End If. Maak dit voor af met een volgende WS.

Oké, dus ik heb een macro met de naam ApplyCF. Ga terug naar Excel, voeg een vorm toe. Makkelijk om hier een vorm te hebben: Invoegen, ik kies altijd een afgeronde rechthoek, typ Reset naar huidige waarden. We passen Home toe, het midden en het midden maken het een beetje groter. Ik hou van de gloed. Ik neem aan dat je het gek vindt om te zien dat het er niet is, de gloed, de instelling die ik leuk vind is er niet, dus ik ga altijd naar Pagina-indeling en effecten en kies die tweede. En als ik dan terug ga naar het formaat, kan ik er een kiezen die echt een beetje glans heeft. Voor mij vind ik dat het er cool uitziet, ik denk dat het het waard is. Klik met de rechtermuisknop, wijs macro toe en zeg ApplyCF, klik op OK. Oké, en wat dit dan zal doen, is dat als ik erop klik, het door al deze bladen gaat, alle formulecellen zoekt en een voorwaardelijke opmaak instelt die zegt: Als deze cellen niet gelijk zijn aan 7,verander de kleur, oké? Dat is het. Het is zo snel, zo snel is het gebeurd. BAM! Het is klaar. En kijk nu of ik deze verander in 11, al die cellen zijn net veranderd. Als het nu teruggaat naar de 1, ahh, zijn de kleuren veranderd. Dus wat de waarde ook was, als we veranderen - als ik deze cel verander, veranderen al die cellen. Als ik deze cel verander, veranderen al die cellen. Als ik deze cel verander, veranderen al die cellen.al die cellen veranderen.al die cellen veranderen.

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.

Nou hey, ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.

Download bestand

Download het voorbeeldbestand hier: Podcast2059.xlsm