Spåra förändringar i formelceller - Excel-tips

Spåra ändringar i Excel-formelceller. Kan du visa vilka objekt som just har ändrats till följd av att vissa ingångsceller har ändrats?

Kolla på video

Spåra förändringar i Excel är lite bisarra.
Målet är att spåra vilka formelceller i Excel som ändras.
Spara som för att spara arbetsbok som XLSM.
Ändra makrosäkerhet.
Spela in ett makro för att räkna ut koden för att ställa in villkorlig formatering för siffror som inte är lika med 2.
Välj den formatering du vill ha.
Spela in ett annat makro för att lära dig hur du tar bort CF från kalkylbladet.
I makrot lägger du till en slinga för varje kalkylblad.
Lägg till ett IF-uttalande för att förhindra att det körs på Titel.
Lägg till en slinga för att kontrollera varje formelcell.
Lägg till villkorlig formatering för att se om cellvärde vid tidpunkten makro körs.
Gå tillbaka till Excel.
Lägg till en form. Tilldela makrot till formen.
Klicka på formen för att köra makroen.
Bonus Tips: Dra en VBA-modul till en ny arbetsbok.

Videoutskrift

Lär dig Excel från Podcast, avsnitt 2059: Excel spår ändringar (i formelresultat)

Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga skickad från Montreal om spårändringar. Spåra ändringar, okej. Så här är vad vi har. Vi har 4 ingångsceller och en hel massa formelceller som är beroende av dessa ingångsceller. Och om jag skulle slå på går jag tillbaka till fliken Granska, aktiverar Markeringsändringar, spårar ändringarna under redigering, klickar på OK, okej. Och de varnade mig för att de måste spara arbetsboken och att makron inte kan användas i delade arbetsböcker. Du vet det? Det här är problemet när du spårar ändringar, de delar arbetsboken och det finns en hel massa saker som inte kan hända i delade arbetsböcker, du vet, som makron och en hel massa andra saker. Men låt oss bara titta på hur spårändringar fungerar i Excel idag.

Låt oss ta den här 2 och ändra från 2 till 22, och ta den här 4 och ändra den från 4 till 44. Okej, och du förstår, vad de har noterat i spårförändringar är att dessa två celler förändrats, okej, de lila trianglarna det faktiska spåret ändras. Alla dessa röda saker, det händer inte men jag illustrerade bara att alla dessa röda blodkroppar förändras och spårändringar säger ingenting om dessa förändringar, okej? Så det står bara att dessa två celler har ändrats men alla dessa andra celler har också ändrats. Och så är frågan från Montreal, finns det ett sätt att få spårförändringar att visa oss allt som förändras, inte bara dessa ingångsceller har förändrats?

Okej, så det första vi måste göra är att stänga av de inbyggda spårändringarna i Excel. Och finns det ett sätt som vi kan få - vi kan bygga vårt eget spårändringssystem som gör att vi kan se alla formelceller som förändrats? Okej, så steg 1 och detta steg är det viktigaste steget, hoppa inte över det här. Titta på din fil, din fil heter något XLSX, du måste spara detta: File, Save As, Som en makroaktiverad arbetsbok, eller inget av detta fungerar. Du måste högerklicka, anpassa menyfliksområdet, aktivera utvecklaren, när du väl har kommit till utvecklaren, gå till Makrosäkerhet, ändra från den här inställningen - den som säger att vi inte ska låta makron springa eller inte ens berätta du att de är där för den här inställningen. Du måste göra dessa två steg. Jag har redan gjort de två stegen. Jag lever varje dag med de två stegen.Redan fixat, men om du är ny på makron är det nytt för dig. Och sedan måste vi ta reda på vilken formatering du vill ha. Okej, så jag ska bara välja några celler här, jag ska spela in en makro som heter HowToCFRed, jag kommer inte att tilldela till en genvägstangent eftersom det här kommer aldrig att köras igen. Jag spelar bara in kod för att ta reda på hur villkorlig formatering fungerar. Och vi kommer in på Hem, Villkorlig formatering, Markera celler som inte är lika med - Så, fler regler, Formatera celler som inte är lika med - Ser du det? Det finns inte i den ursprungliga rullgardinsmenyn, men om du kommer in här, inte lika med 2, och välj sedan formatet. Detta är den viktiga delen. Så jag ska välja en röd bakgrund. Du väljer vilken färg du vill ha här, okej? Gå till och med till Fler färger, välj några andra röda,gå in i Custom, välj någon annan röd, okej? Det är skönheten i Macro Recorder, de kommer att få oss något perfekt rött för dig eller blått eller vad det än är du vill ha. Okej, klicka på OK. Och sedan ska vi sluta spela in, okej. Återigen, hela poängen med detta är bara att se vad koden är för villkorliga format.

Jag ska till makron, hur villkorligt format rött och redigera. Okej, så här är de viktiga delarna av den här koden. Jag kan se att de lägger till ett villkorligt format med xlNotEqual och vi citerar svårt att det inte är lika med 2. Och sedan ändrar vi cellens inre till den färgen.

Okej, jag måste också ta reda på hur jag tar bort all villkorlig formatering på arket. Så, tillbaka till Excel, spela in en annan makro, hur man tar bort alla villkorliga, OK. Kom hit till fliken Hem, gå till Villkorlig formatering, Rensa regel från hela arket, Stoppa inspelning och vi tittar på den koden. Bra, det är ett makro i en rad. Och jag gillar även här att hur de gör det för hela arket är att det bara hänvisar till celler. Så med andra ord, alla celler på det aktiva arket.

Nu måste jag göra detta makro, det inspelade makrot, lite mer generiskt. Och jag har skrivit massor av böcker om hur man gör VBA i Excel och jag har gjort videor om hur man gör VBA i Excel, och här är det enkla: du måste kunna spela in ett makro som detta men lägg sedan till ungefär fem eller sex rader för att kunna göra makrot tillräckligt.

Och jag ska prata om dessa rader, okej. Så det första jag vill göra är att jag vill säga, jag vill gå igenom den aktiva arbetsboken, gå igenom alla kalkylblad. Så för varje kalkylblad är WS objektvariabeln, jag går igenom alla kalkylblad. Och personen från Montreal sa, "Hej, det finns ett ark som jag inte vill att det ska hända på." Så om WS.Name, med kalkylbladets punktnamn, inte är lika med titeln, kommer vi att göra koden i makrot. Här är bladets namn: .Cells.FormatConditions.Delete. Så vi kommer att gå igenom varje individ på arket förutom titeln och ta bort alla formatvillkor, då ska vi gå igenom varje cell i arket men inte alla celler, bara cellerna som har formler . Om det inte har en formel så gör jag inteJag behöver inte formatera det eftersom det inte kommer att förändras. Cell.FormatConditions.Add, detta är direkt från makrot även om det inspelade makrot sa Selection - Jag vill inte behöva markera det så jag ska bara säga Cell, det är varje enskild cell. Vi ska använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS. s kommer inte att förändras. Cell.FormatConditions.Add, detta är direkt från makrot även om det inspelade makrot sa Selection - Jag vill inte behöva markera det så jag ska bara säga Cell, det är varje enskild cell. Vi ska använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.s kommer inte att förändras. Cell.FormatConditions.Add, detta är direkt från makrot även om det inspelade makrot sa Selection - Jag vill inte behöva markera det så jag ska bara säga Cell, det är varje enskild cell. Vi ska använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2, säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5, säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.detta är direkt från makrot även om det inspelade makrot sa Selection - Jag vill inte behöva välja det så jag ska bara säga Cell, det är varje enskild cell. Vi ska använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.detta är direkt från makrot även om det inspelade makrot sa Selection - Jag vill inte behöva välja det så jag ska bara säga Cell, det är varje individuell cell. Vi kommer att använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.Jag vill inte välja det så jag säger bara Cell, det är varje enskild cell. Vi ska använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.Jag vill inte välja det så jag säger bara Cell, det är varje enskild cell. Vi ska använda xlNotEqual och istället för Formel: = ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.= ”=” 2 vilket är vad den inspelade koden gjorde precis där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2, säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5, säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.= ”=” 2 vilket är vad den inspelade koden gjorde just där, jag har sammanfogat vad som helst i den cellen. Så kontrollera om det inte är lika med det aktuella värdet. Så om cellen för närvarande har 2 säger vi att den inte är lika med 2. Om cellen för närvarande har 16,5 säger vi att den inte är lika med 16,5. Och sedan är resten av detta bara rakt inspelat makro, inspelat makro, inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.inspelat makro, inspelat makro. Allt detta är från ett inspelat makro. Avsluta detta om med ett slut om. Avsluta detta för med en nästa WS.

Okej, så jag har ett makro som heter ApplyCF. Gå tillbaka till Excel, lägg till en form. Lätt att ha en form här: Infoga, jag väljer alltid en rundad rektangel, skriv Återställ till aktuella värden. Vi kommer att tillämpa Hem, centrum och centrum gör det lite större. Jag älskar glödet. Jag antar att du tycker att det är dumt att se att det inte finns, glödet, inställningen jag gillar inte finns där så jag går alltid till sidlayout och effekter och väljer den andra. Och när jag går tillbaka till formatet kan jag välja en som faktiskt har lite glöd. För mig tycker jag att det ser coolt ut, jag tycker att det är värt det. Högerklicka, tilldela makro och säg ApplyCF, klicka på OK. Okej, och vad det här gör är när jag klickar på det, det går igenom alla dessa ark, hittar alla formelceller och ställer in en villkorlig formatering som säger: Om dessa celler inte är lika med 7,ändra färg, okej? Det är allt. Det är så snabbt det, hände så snabbt. BAM! Det är gjort. Och nu, se om jag ändrar den här till 11, alla dessa celler har just ändrats. Om det nu går tillbaka till 1, ahh, ändrades färgerna. Så, oavsett värdet, när vi ändrar - om jag ändrar den här cellen ändras alla dessa celler. Om jag ändrar den här cellen ändras alla dessa celler. Om jag ändrar den här cellen ändras alla dessa celler.alla dessa celler förändras.alla dessa celler förändras.

Okej, nu är detta den nya normalen. Nu härifrån vill jag spåra igen. Så jag återställer till aktuella värden och sedan om jag ändrar den här till en 3, ändras försäljningen. Åh, förresten, dessa celler här och dessa andra ark förändrades också som svar på detta. Spåra ändringar i Excel när det finns? Ja, det är verkligen halt. Det visar dig inte de saker som förändrats och att behöva visa arbetsboken är en hemsk, hemsk sak. Men med detta enkla, enkla lilla makro fungerar det.

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.

Tja hej, jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.

Nedladdning fil

Ladda ner exempelfilen här: Podcast2059.xlsm