Track Changes in Formula Cells

Learn Excel from MrExcel Podcast, Episode 2059: Excel Track Changes (in Formula results) 

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Today's question sent in from Montreal about track changes.  Track changes, alright.  So here's what we have.  We have 4 Input Cells, and a whole bunch of Formula cells that rely on these Input Cells.  And if I would turn on, I'll go back to the Review tab, turn on Highlight Changes, Track the changes while editing, click OK, alright.  And they warned me that they have to save the workbook and that macros cannot be used in shared workbooks.  You know that?  This is the problem when you track changes, they share the workbook and there's a whole bunch of things that can't happen in shared workbooks, you know, like macros and a whole bunch of other things.  But let's just take a look at how track changes works in Excel today.

Let's take this 2 and change from 2 to 22, and take this 4 and change it from 4 to 44.  Alright, and you see, what they've noted in track changes is that these two cells changed, alright, those purple triangles are the actual track changes.  All of these red stuff, that does not happen but I just illustrated that all of these red cells are changing and track changes says nothing about these changes, alright?  So, it just says, these two cells were changed but all of these other cells were also changed.  And so then the question from Montreal is, is there a way to have track changes actually show us everything that change, not just these input cells have changed?

Alright, so, first thing we have to do is turn off the Excel built-in Track Changes.  And then, is there a way that we can get- we can build our own track changes system that will allow us to see all of the formula cells that changed?  Alright, so Step 1 and this step is the most important step, do not skip this.  Look at your file, your file is called something XLSX, you have to save this: File, Save As, As a macro enabled workbook , or none of this will work.  You have to right-click, Customize the Ribbon, turn on Developer, once you get to Developer, go to Macro Security, change from this setting - the one that says we're not going to let macros run or not even going to tell you that they're there to this setting.  You have to do those two steps.  I've already done those two steps.  I live every day with those two steps.  Already fixed, but if you're new to macros, this is new to you.  And then, we need to figure out what kind of formatting you want.  Alright, so I'm just going to choose some cells here, I'm going to Record a Macro that's called HowToCFRed, I’m not going to assign into a shortcut key because this is never going to run again.  I'm just recording code to figure out how conditional formatting works.  And we'll get into Home, Conditional Formatting, Highlight Cells that are not equal to – So, More Rules, Format cells not equal to - See that?  It's not in the original drop-down but if you come in here, not equal to 2, and then choose the format.  This is the important part.  So I'm going to choose a red background.  You choose whatever color you want here, alright?  Even go to More Colors, choose some other red, go into Custom, choose some other red, alright?  That's the beauty of the Macro Recorder, they are going to get us some perfect red for you or blue or whatever it is that you want.  Alright, click OK.  And then, we're going to stop recording, alright.  Again, the whole point of this is just to see what the code is for conditional formats. 

I'm going to Macros, How to conditional format red, and edit.  Alright, so here's the important parts of this code.  I can see they're adding a conditional format using the xlNotEqual and we're hard quoting it to not be equal to 2.  And then we're changing the interior of the cell to that color. 

Alright, I also need to figure out how to delete all conditional formatting on the sheet. So, back to Excel, Record another Macro, How to delete all conditional, OK.  Come here to the Home tab, go to Conditional Formatting, Clear Rule from the entire sheet, Stop Recording and we'll go look at that code.  Great, it's a one-line macro.  And I even like here that the way they do it for the entire sheet is it just refer to cells. So in other words, all the cells on the active sheet. 

Now, I need to make this macro, the recorded macro, a little bit more generic.  And I've written lots of books about how to do VBA in Excel and I've done videos on how to do VBA in Excel, and here's the simple thing: you need to be able to record a macro like this but then, add about five or six lines in order to be able to make the macro generic enough. 

And I'm going to talk about those lines, alright.  So the first thing I want to do is I want to say, I want to go through the active workbook, go through all of the worksheets.  So for each worksheet, WS is the object variable, I'll go through all the worksheets.  And the person from Montreal said, “Hey, there is one sheet that I don't want to have this happen on.”  So, if the WS.Name, with the worksheet dot name, is not equal to Title then we're going to do the code in the macro.  Here's the sheet name: .Cells.FormatConditions.Delete.  So, we're going to go through each individual of the sheet except for the title and delete all the format conditions, then we're going to go through each cell in the sheet but not all the cells, just the cells that have formulas.  If it doesn't have a formula then I don't need to format it because it's not going to change.  Cell.FormatConditions.Add, this is directly from the macro although the recorded macro said Selection - I don't want to have to select it so I'm just going to say Cell, that's each individual cell.  We're going to use the xlNotEqual and instead of Formula:=”=”2 which is what the recorded code did right there, I've concatenated whatever's in that cell.  So checking to see if it's not equal to the current value.  So if the cell currently has 2, we're saying not equal to 2.  If the cell currently has 16.5, we're saying not equal to 16.5.  And then the rest of this is just straight recorded macro, recorded macro, recorded macro, recorded macro.  All of that is from a recorded macro.  Finish this If with an End If.  Finish this For with a Next WS. 

Alright, so I have a macro called ApplyCF.  Go back to Excel, add a shape.  Easy to have a shape here: Insert, I always choose a rounded rectangle, type Reset To Current Values.  We’ll apply Home, the center, and the center make it a little bit larger.  I love the glow.  I suppose you think it's silly seeing it's not there, the glow, the setting I like isn't there so I always go to Page Layout and Effects and choose that second one.  And then when I go back to the format, I can choose one that actually has a little bit of glow.  To me, I think it looks cool, I think it's worth it.  Right-click, Assign Macro and say ApplyCF, click OK.  Alright, and then what this will do is when I click it, it’ll go through all of these sheets, find all of the formula cells and set up a conditional formatting that says: If these cells not equal to 7, change the color, alright?  That's it.  It's that fast it, happened that fast.  BAM! It's done.  And now, watch if I change this one to 11, all of those cells just changed.  Now if it goes back to the 1, ahh, the colors changed.  So, whatever the value was, when we change- if I change this cell, all of those cells change.  If I change this cell, all of those cells change.  If I change this cell, all of those cells change.

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.

Well hey, I want to thank you for stopping by.  We’ll see you next time for another netcast from MrExcel.