Spor endringer i formelceller - Excel-tips

Spor endringer i Excel-formelceller. Kan du vise hvilke elementer som nettopp er endret som et resultat av å endre bestemte inndataceller?

Se på video

Spor endringer i Excel er litt bisarr.
Målet er å spore hvilke formelceller i Excel som endres.
Lagre som for å lagre arbeidsbok som XLSM.
Endre makrosikkerhet.
Ta opp en makro for å finne ut koden for å sette opp betinget formatering for tall som ikke er lik 2.
Velg formateringen du vil ha.
Ta opp en annen makro for å lære hvordan du fjerner CF fra regnearket.
I makroen legger du til en sløyfe for hvert regneark.
Legg til en IF-setning for å forhindre at den kjører på tittel.
Legg til en sløyfe for å sjekke hver formelcelle.
Legg til betinget formatering for å se om celleverdien på det tidspunktet makroen kjører.
Gå tilbake til Excel.
Legg til en figur. Tilordne makroen til formen.
Klikk på figuren for å kjøre makroen.
Bonustips: Dra en VBA-modul til en ny arbeidsbok.

Videoutskrift

Lær Excel fra Podcast, Episode 2059: Excel Track Changes (i formelresultater)

Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål sendt inn fra Montreal om sporendringer. Spor endringer, ok. Så her er hva vi har. Vi har 4 inngangsceller, og en hel haug med formelceller som er avhengige av disse inngangscellene. Og hvis jeg ville slå på, går jeg tilbake til kategorien Gjennomgang, slår på Fremhev endringer, Spor endringene mens du redigerer, klikk OK, OK. Og de advarte meg om at de må lagre arbeidsboken, og at makroer ikke kan brukes i delte arbeidsbøker. Du vet det? Dette er problemet når du sporer endringer, de deler arbeidsboken, og det er en hel mengde ting som ikke kan skje i delte arbeidsbøker, du vet, som makroer og en hel haug med andre ting. Men la oss bare se på hvordan sporendringer fungerer i Excel i dag.

La oss ta denne 2 og endre fra 2 til 22, og ta denne 4 og endre den fra 4 til 44. Greit, og du skjønner, det de har lagt merke til i sporendringene er at disse to cellene endret seg, ok, de lilla trekanter er selve sporet endres. Alle disse røde tingene, det skjer ikke, men jeg illustrerte bare at alle disse røde blodcellene endrer seg, og sporendringer sier ingenting om disse endringene, ok? Så det står bare at disse to cellene ble endret, men alle disse andre cellene ble også endret. Og så er spørsmålet fra Montreal, er det en måte å få sporendringer til å vise oss alt som endres, ikke bare disse inngangscellene har endret seg?

Ok, så det første vi må gjøre er å slå av de innebygde sporendringene i Excel. Og så, er det en måte vi kan få - vi kan bygge vårt eget sporendringssystem som lar oss se alle formelcellene som endret seg? OK, så trinn 1 og dette trinnet er det viktigste trinnet. Ikke hopp over dette. Se på filen din, filen din heter noe XLSX, du må lagre denne: Fil, Lagre som, Som en makroaktivert arbeidsbok, eller ingenting av dette vil fungere. Du må høyreklikke, tilpasse båndet, slå på utvikler, når du kommer til utvikler, gå til makrosikkerhet, endre fra denne innstillingen - den som sier at vi ikke skal la makroer kjøre eller ikke engang fortelle du at de er der for denne innstillingen. Du må gjøre de to trinnene. Jeg har allerede gjort de to trinnene. Jeg lever hver dag med de to trinnene.Allerede løst, men hvis du er ny innen makroer, er dette nytt for deg. Og så må vi finne ut hva slags formatering du vil ha. Ok, så jeg skal bare velge noen celler her, jeg skal spille inn en makro som heter HowToCFRed, jeg skal ikke tilordne til en hurtigtast fordi dette aldri kommer til å kjøre igjen. Jeg registrerer bare kode for å finne ut hvordan betinget formatering fungerer. Og vi kommer inn på Hjem, Betinget formatering, Fremhev celler som ikke er like - Så, Flere regler, Formater celler ikke like - Ser du det? Det er ikke i den opprinnelige rullegardinmenyen, men hvis du kommer inn her, ikke lik 2, og deretter velger du formatet. Dette er den viktige delen. Så jeg skal velge rød bakgrunn. Du velger hvilken farge du vil ha her, ok? Du kan til og med gå til Flere farger, velge noen andre røde,gå inn i Custom, velg noen andre røde, ok? Det er skjønnheten til Macro Recorder, de skal gi oss noe perfekt rødt for deg eller blått eller hva det er du vil ha. OK, klikk OK. Og så skal vi slutte å ta opp, ok. Igjen, hele poenget med dette er bare å se hva koden er for betingede formater.

Jeg skal til Makroer, Hvordan betinget format rødt, og redigere. Ok, så her er de viktige delene av denne koden. Jeg kan se at de legger til et betinget format ved hjelp av xlNotEqual, og vi siterer vanskelig med å sitere det for ikke å være lik 2. Og så endrer vi det indre av cellen til den fargen.

OK, jeg må også finne ut hvordan jeg kan slette all betinget formatering på arket. Så, tilbake til Excel, ta opp en annen makro, Slik sletter du alle betingede, OK. Kom hit til Hjem-fanen, gå til Betinget formatering, Fjern regel fra hele arket, Stopp opptak, så ser vi på den koden. Flott, det er en makro på en linje. Og jeg liker til og med her at slik de gjør det for hele arket, er det bare å referere til celler. Så med andre ord, alle cellene på det aktive arket.

Nå må jeg lage denne makroen, den innspilte makroen, litt mer generisk. Og jeg har skrevet mange bøker om hvordan du gjør VBA i Excel, og jeg har laget videoer om hvordan du gjør VBA i Excel, og her er det enkle: du må kunne spille inn en makro som dette, men legg til omtrent fem-seks linjer for å kunne gjøre makroen generisk nok.

Og jeg skal snakke om disse linjene, ok. Så det første jeg vil gjøre er at jeg vil si, jeg vil gå gjennom den aktive arbeidsboken, gå gjennom alle regnearkene. Så for hvert regneark er WS objektvariabelen, jeg går gjennom alle regnearkene. Og personen fra Montreal sa: "Hei, det er ett ark som jeg ikke vil at dette skal skje på." Så hvis WS.Name, med regnearkets punktnavn, ikke er lik Tittel, skal vi gjøre koden i makroen. Her er arknavnet: .Cells.FormatConditions.Delete. Så vi skal gå gjennom hvert individ på arket bortsett fra tittelen og slette alle formatforholdene, så skal vi gå gjennom hver celle i arket, men ikke alle cellene, bare cellene som har formler . Hvis den ikke har en formel, har jeg ikkeJeg trenger ikke å formatere det fordi det ikke kommer til å endres. Cell.FormatConditions.Add, dette er direkte fra makroen selv om den innspilte makroen sa Selection - Jeg ønsker ikke å måtte velge den, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS. kommer ikke til å endres. Cell.FormatConditions.Add, dette er direkte fra makroen selv om den innspilte makroen sa Selection - Jeg ønsker ikke å måtte velge den, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.kommer ikke til å endres. Cell.FormatConditions.Add, dette er direkte fra makroen selv om den innspilte makroen sa Selection - Jeg vil ikke behøve å velge den, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett opptatt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.dette er direkte fra makroen, selv om den innspilte makroen sa Selection - Jeg vil ikke måtte velge den, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett opptatt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.dette er direkte fra makroen, selv om den innspilte makroen sa Selection - Jeg vil ikke måtte velge den, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.Jeg vil ikke måtte velge det, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.Jeg vil ikke måtte velge det, så jeg skal bare si Cell, det er hver enkelt celle. Vi skal bruke xlNotEqual, og i stedet for Formel: = ”=” 2, det er hva den registrerte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.= ”=” 2 som er hva den innspilte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.= ”=” 2 som er hva den innspilte koden gjorde akkurat der, jeg har sammenkoblet det som er i den cellen. Så sjekk for å se om det ikke er lik gjeldende verdi. Så hvis cellen for øyeblikket har 2, sier vi at den ikke er lik 2. Hvis cellen for øyeblikket har 16,5, sier vi at den ikke er lik 16,5. Og så er resten av dette bare rett innspilt makro, innspilt makro, innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.innspilt makro, innspilt makro. Alt dette er fra en registrert makro. Avslutt dette hvis med en slutt hvis. Avslutt dette for med en neste WS.

Ok, så jeg har en makro som heter ApplyCF. Gå tilbake til Excel, legg til en figur. Lett å ha en form her: Sett inn, jeg velger alltid et avrundet rektangel, skriv Tilbakestill til gjeldende verdier. Vi vil bruke Hjem, sentrum og senter for å gjøre det litt større. Jeg elsker gløden. Jeg antar at du synes det er dumt å se at det ikke er der, gløden, innstillingen jeg liker ikke er der, så jeg går alltid til Sideoppsett og effekter og velger den andre. Og så når jeg går tilbake til formatet, kan jeg velge en som faktisk har litt glød. For meg synes jeg det ser kult ut, jeg synes det er verdt det. Høyreklikk, Tilordne makro og si ApplyCF, klikk OK. Ok, og hva dette vil gjøre er når jeg klikker på det, det går gjennom alle disse arkene, finner alle formelcellene og setter opp en betinget formatering som sier: Hvis disse cellene ikke er like 7,endre farge, ok? Det er det. Det er så fort det, skjedde så fort. BAM! Det er gjort. Og nå, se om jeg endrer denne til 11, alle disse cellene endret seg bare. Nå hvis det går tilbake til 1, ahh, endret fargene. Så uansett hvilken verdi var, når vi endrer - hvis jeg endrer denne cellen, endres alle disse cellene. Hvis jeg endrer denne cellen, endres alle disse cellene. Hvis jeg endrer denne cellen, endres alle disse cellene.alle disse cellene endres.alle disse cellene endres.

OK, nå er dette den nye normalen. Nå herfra vil jeg spore igjen. Så jeg tilbakestiller til gjeldende verdier, og hvis jeg endrer denne til en 3, endres salgene. Å, forresten, disse cellene her og disse andre arkene endret seg også som svar på dette. Spor endringer i Excel slik det eksisterer? Ja, det er veldig halt. Det viser deg ikke de tingene som endret seg, og å måtte vise arbeidsboken er en fryktelig, fryktelig ting. Men med denne enkle, enkle lille makroen fungerer 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.

Vel hei, jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.

Last ned fil

Last ned eksempelfilen her: Podcast2059.xlsm