Spor ændringer i formelceller - Excel-tip

Spor ændringer i Excel-formelceller. Kan du vise, hvilke elementer der lige er ændret som et resultat af ændring af visse inputceller?

Se video

Spor ændringer i Excel er lidt bizart.
Målet er at spore, hvilke formelceller i Excel der ændres.
Gem som for at gemme projektmappe som XLSM.
Skift makrosikkerhed.
Optag en makro for at finde ud af koden for at oprette betinget formatering for tal, der ikke er lig med 2.
Vælg den ønskede formatering.
Optag en anden makro for at lære at fjerne CF fra regnearket.
I makroen skal du tilføje en løkke til hvert regneark.
Tilføj en IF-sætning for at forhindre, at den kører på titel.
Tilføj en sløjfe for at kontrollere hver formelcelle.
Tilføj betinget formatering for at se, om celleværdien på tidspunktet kører makro.
Gå tilbage til Excel.
Tilføj en figur. Tildel makroen til formen.
Klik på figuren for at køre makroen.
Bonus Tip: Træk et VBA-modul til en ny projektmappe.

Videoudskrift

Lær Excel fra Podcast, afsnit 2059: Excel spor ændringer (i formelresultater)

Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål sendt fra Montreal om sporændringer. Spor ændringer, okay. Så her er hvad vi har. Vi har 4 inputceller og en hel masse formelceller, der er afhængige af disse inputceller. Og hvis jeg vil tænde, går jeg tilbage til fanen Gennemse, tænder Fremhæv ændringer, Spor ændringerne under redigering, klik OK, okay. Og de advarede mig om, at de skal gemme projektmappen, og at makroer ikke kan bruges i delte projektmapper. Du ved det? Dette er problemet, når du sporer ændringer, de deler projektmappen, og der er en hel masse ting, der ikke kan ske i delte projektmapper, du ved, som makroer og en hel masse andre ting. Men lad os bare se på, hvordan sporændringer fungerer i Excel i dag.

Lad os tage denne 2 og ændre fra 2 til 22, og tage denne 4 og ændre den fra 4 til 44. Okay, og du kan se, hvad de har bemærket i sporændringer er, at disse to celler ændrede sig, okay, de lilla trekanter er de faktiske sporændringer. Alle disse røde ting, det sker ikke, men jeg illustrerede bare, at alle disse røde celler ændrer sig, og sporændringer siger intet om disse ændringer, okay? Så det står bare, at disse to celler blev ændret, men alle disse andre celler blev også ændret. Og så er spørgsmålet fra Montreal, er der en måde at få sporændringer til at vise os alt, hvad der ændrer sig, ikke kun disse inputceller har ændret sig?

Okay, så den første ting, vi skal gøre, er at slukke for de indbyggede Excel-ændringer. Og så er der en måde, vi kan få - vi kan bygge vores eget sporskiftesystem, der giver os mulighed for at se alle de formelceller, der ændrede sig? Okay, så trin 1 og dette trin er det vigtigste trin. Spring ikke over dette. Se på din fil, din fil hedder noget XLSX, du skal gemme dette: Fil, Gem som, Som en makroaktiveret projektmappe, eller intet af dette fungerer. Du skal højreklikke, tilpasse båndet, slå Developer til, når du kommer til Developer, gå til Macro Security, skift fra denne indstilling - den der siger, at vi ikke vil lade makroer køre eller ikke engang fortælle dig, at de er der til denne indstilling. Du skal gøre disse to trin. Jeg har allerede gjort de to trin. Jeg lever hver dag med de to trin.Allerede rettet, men hvis du er ny inden for makroer, er dette nyt for dig. Og så skal vi finde ud af, hvilken form for formatering du vil have. Okay, så jeg skal bare vælge nogle celler her, jeg skal optage en makro, der hedder HowToCFRed, jeg vil ikke tildele til en genvejstast, fordi dette aldrig kommer til at køre igen. Jeg optager bare kode for at finde ud af, hvordan betinget formatering fungerer. Og vi kommer ind i hjemmet, betinget formatering, fremhæver celler, der ikke er lig med - Så flere regler, formater celler ikke lig med - ser du det? Det er ikke i den oprindelige rullemenu, men hvis du kommer ind her, ikke lig med 2, og vælg derefter formatet. Dette er den vigtige del. Så jeg skal vælge en rød baggrund. Du vælger hvilken farve du vil have her, okay? Gå endda til Flere farver, vælg et andet rødt,gå ind i brugerdefineret, vælg nogle andre røde, okay? Det er skønheden i Macro Recorder, de vil give os noget perfekt rødt til dig eller blå eller hvad det er, du vil have. OK, klik på OK. Og så stopper vi optagelsen, okay. Igen er hele pointen med dette bare at se, hvad koden er til betingede formater.

Jeg går til makroer, hvordan betinget format rødt, og rediger. Okay, så her er de vigtige dele af denne kode. Jeg kan se, at de tilføjer et betinget format ved hjælp af xlNotEqual, og vi citerer det hårdt for ikke at være lig med 2. Og så ændrer vi det indre af cellen til den farve.

Okay, jeg skal også finde ud af, hvordan jeg sletter al betinget formatering på arket. Så tilbage til Excel, optag en anden makro, Sådan slettes alle betingede, OK. Kom her til fanen Hjem, gå til Betinget formatering, Ryd regel fra hele arket, Stop optagelse, så ser vi på den kode. Fantastisk, det er en makro med en linje. Og jeg kan endda godt lide her, at den måde, de gør det på hele arket, er, at det bare refererer til celler. Så med andre ord, alle cellerne på det aktive ark.

Nu skal jeg gøre denne makro, den optagede makro, lidt mere generisk. Og jeg har skrevet mange bøger om, hvordan man laver VBA i Excel, og jeg har lavet videoer om, hvordan man laver VBA i Excel, og her er den enkle ting: du skal kunne optage en makro som denne, men tilføj derefter omkring fem eller seks linjer for at være i stand til at gøre makroen generisk nok.

Og jeg vil tale om disse linjer, okay. Så det første, jeg vil gøre, er, at jeg vil sige, jeg vil gennemgå den aktive projektmappe, gennemgå alle regnearkene. Så for hvert regneark er WS objektvariablen, jeg gennemgår alle regnearkene. Og personen fra Montreal sagde: ”Hej, der er et ark, som jeg ikke ønsker, at dette skal ske på.” Så hvis WS.Name med regnearkets punktnavn ikke er lig med titel, så laver vi koden i makroen. Her er arknavnet: .Cells.FormatConditions.Delete. Så vi gennemgår hver enkelt af arket bortset fra titlen og sletter alle formatbetingelser, så går vi gennem hver celle i arket, men ikke alle cellerne, bare de celler, der har formler . Hvis det ikke har en formel, så gør jeg ikke 'behøver ikke at formatere det, fordi det ikke vil ændre sig. Cell.FormatConditions.Add, dette er direkte fra makroen, selvom den optagede makro sagde Selection - Jeg ønsker ikke at skulle vælge den, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS. s vil ikke ændre sig. Cell.FormatConditions.Add, dette er direkte fra makroen, selvom den optagede makro sagde Selection - Jeg ønsker ikke at skulle vælge den, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.s vil ikke ændre sig. Cell.FormatConditions.Add, dette er direkte fra makroen, selvom den optagede makro sagde Selection - Jeg ønsker ikke at skulle vælge den, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.dette er direkte fra makroen, selvom den optagede makro sagde Selection - Jeg vil ikke have at vælge det, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.dette er direkte fra makroen, selvom den optagede makro sagde Selection - Jeg vil ikke have at vælge det, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.Jeg ønsker ikke at skulle vælge det, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.Jeg ønsker ikke at skulle vælge det, så jeg vil bare sige Cell, det er hver enkelt celle. Vi skal bruge xlNotEqual og i stedet for Formel: = ”=” 2, hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.= ”=” 2 hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.= ”=” 2 hvilket er hvad den registrerede kode gjorde lige der, jeg har sammenkædet hvad der er i den celle. Så tjek for at se om det ikke er lig med den aktuelle værdi. Så hvis cellen i øjeblikket har 2, siger vi, at den ikke er lig med 2. Hvis cellen i øjeblikket har 16,5, siger vi, at den ikke er lig med 16,5. Og så er resten af dette bare lige optaget makro, optaget makro, optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.optaget makro, optaget makro. Alt dette er fra en optaget makro. Afslut dette hvis med en afslutning hvis. Afslut dette for med en næste WS.

Okay, så jeg har en makro, der hedder ApplyCF. Gå tilbage til Excel, tilføj en figur. Let at have en form her: Indsæt, jeg vælger altid et afrundet rektangel, skriv Nulstil til aktuelle værdier. Vi anvender Hjemmet, centret og centret gør det lidt større. Jeg elsker glød. Jeg formoder, at du synes, det er fjollet at se, at det ikke er der, gløden, den indstilling, jeg kan lide, ikke er der, så jeg går altid til Sidelayout og effekter og vælger den anden. Og så når jeg går tilbage til formatet, kan jeg vælge et, der faktisk har lidt glød. For mig synes jeg, det ser sejt ud, jeg synes det er det værd. Højreklik, Tildel makro og sig ApplyCF, klik OK. Okay, og hvad det her vil gøre er, når jeg klikker på det, det gennemgår alle disse ark, finder alle formelcellerne og opretter en betinget formatering, der siger: Hvis disse celler ikke er lig med 7,ændre farve, okay? Det er det. Det er så hurtigt det, skete så hurtigt. BAM! Det er gjort. Og se nu, hvis jeg ændrer denne til 11, ændrede alle disse celler sig bare. Hvis det nu går tilbage til 1, ahh, ændrede farverne sig. Så uanset hvilken værdi der var, når vi ændrer - hvis jeg ændrer denne celle, ændres alle disse celler. Hvis jeg ændrer denne celle, ændres alle disse celler. Hvis jeg ændrer denne celle, ændres alle disse celler.alle disse celler ændres.alle disse celler ændres.

Okay, nu er dette den nye normale. Nu herfra vil jeg spore igen. Så jeg nulstiller til aktuelle værdier, og hvis jeg ændrer denne til en 3, ændres disse salg. Åh, forresten, disse celler tilbage her, og disse andre ark ændrede sig også som svar på dette. Spor ændringer i Excel, da den findes? Ja, det er virkelig halt. Det viser dig ikke de ting, der ændrede sig, og det er en frygtelig, forfærdelig ting at skulle vise projektmappen. Men med denne enkle, enkle lille makro 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.

Nå hej, jeg vil gerne takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.

Download fil

Download eksempelfilen her: Podcast2059.xlsm