MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Track Changes in Formula Cells


October 30, 2017 - by Bill Jelen

Track Changes in Formula Cells

Track Changes in Excel Formula Cells. Can you show which items just changed as a result of changing certain input cells?


Watch Video

  • Track changes in Excel is a little bizarre.
  • Goal is to track what formula cells in Excel change.
  • Save As to save workbook as XLSM.
  • Change Macro Security.
  • Record a macro to figure out the code to set up conditional formatting for numbers not equal to 2.
  • Choose the Formatting that you want.
  • Record another macro to learn how to remove CF from worksheet.
  • In the macro, add a loop for each worksheet.
  • Add an IF statement to prevent it from running on Title.
  • Add a loop to check each formula cell.
  • Add Conditional Formatting to see if cell <> value at time macro runs.
  • Go back to Excel.
  • Add a shape. Assign the macro to the shape.
  • Click the Shape to Run the Macro.
  • Bonus Tip: Dragging a VBA module to a new workbook.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2059 Excel track changes in
  • formula results hey welcome back to the
  • MrExcel NetCast I'm Bill Jelen and
  • today's question sent in from Montreal
  • about track changes track changes
  • alright so here's what we have we have
  • four input sales and a whole bunch of
  • form of the 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 this is the
  • problem when you track changes they
  • share the workbook and there's a whole
  • bunch of things that can't happen and
  • 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 too
  • 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
  • all right those purple triangles are the
  • actual track changes all of this red
  • stuff that does not happen but I just
  • illustrated that all of these red cells
  • are changing and track change that says
  • nothing about these changes all right 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 to change not just these
  • input cells have changed all right so
  • first thing we have to do is turn off
  • the Excel building track changes you
  • know 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 forms of cells that changed alright
  • so step one in this step is the most
  • important step do not skip this look at
  • your file your file is called something
  • dot 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 gonna let macros
  • run we're not even gonna tell you that
  • they're 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 and then we
  • need to figure out what kind of
  • formatting you want all right so I'm
  • just gonna choose some cells here I'm
  • gonna record a macro that's called how
  • to CF read I'm not gonna sign into a
  • shortcut key because this is never gonna
  • 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 some 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 two and then
  • choose the format this is the important
  • part so I'm gonna choose a red
  • background you choose whatever color you
  • want here
  • right even go to more colors choose some
  • other red go into custom choose some of
  • the red right 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 all right
  • click OK and then we're gonna 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 read
  • and edit
  • alright so here's the important parts of
  • this code I can see they're adding a
  • conditional format using the Excel not
  • equal and we're hard coding it to not be
  • equal to two 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
  • sheep so back to Excel record another
  • back row
  • how did the lead all conditional
  • formatting okay 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 sell some of the 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
  • BBA 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 gonna talk
  • about those lives 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 an object variable
  • I'll go through all the worksheets and
  • the person from Montreal said hey
  • there's one sheet that I don't want to
  • have this happen on so if the WS dot
  • name is worksheet name is not equal to
  • title then we're gonna do the code in
  • the macro here's the sheet name dot
  • cells dot format conditions not delete
  • so we're gonna go through each
  • individual 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 it doesn't
  • have a formula then I don't need to
  • format it because it's not gonna change
  • cell dot format conditions not 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
  • gonna say cell that's each individual
  • cell we're gonna use the excel not equal
  • and instead of formula colon equals
  • equals two 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 two
  • we're saying not equal to two if the
  • cell currently has sixteen
  • five we're saying not equal to sixteen
  • point five and then the rest of this is
  • just straight recorded macro recording
  • macro recorded macro recording macro all
  • of us from the record of macro finish
  • this if with an end if finish this four
  • with a next ws all right so I have a
  • macro called apply CF go back to excel
  • add a shape easy to have shape here
  • insert I always choose a rounded
  • rectangle type reset to current values
  • will apply home the center and the
  • center make it a little bit larger
  • I love the glow I suppose you think it's
  • silly seen it's not there the glow the
  • setting I like isn't there so I always
  • got a 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
  • apply CF click OK alright and then what
  • this will do is when I click it I'll go
  • through all of these sheets find all of
  • the form of the cells and set up a
  • conditional formatting that says if this
  • cells not equal to seven change the
  • color right that's it it's that fast it
  • happened that fast BAM it's done and now
  • watch if I change this one to eleven all
  • of those cells have just changed now if
  • it goes back to the one the colors
  • change so it's whatever the value was
  • when we changed if I change this cell
  • all those cells change if I change this
  • cell all of those cells change if I
  • change the cell all of those cells
  • change alright now this is the new
  • normal
  • now from here I want to track again it's
  • our reset to current values and then if
  • I change this one to a three those sales
  • change well by the way these cells back
  • here and these other sheets also changed
  • in response to this track changes in
  • Excel as it has it exists yeah it's it's
  • really lame it it doesn't show you the
  • things have changed and having to share
  • the workbook is a horrible horrible
  • thing but with this simple simple little
  • macro it
  • works right now the question said all
  • right 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 all right 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 all
  • right so we changed that cell those four
  • cells are all formulas cells now if I
  • want this workbook book to 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 book two and
  • then I can see podcast 205 nine I simply
  • take that module and drag it and drop it
  • on book two right there and now that
  • code is also in book two coming back to
  • book two just add a shape right click
  • assign macro like okay all right it
  • works and then three 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 I wonder if I could
  • create something that would actually
  • track the changes what are the downsides
  • here I'm sure I'm going to hear about
  • this in the YouTube comments if you had
  • ten thousand form of the cells well now
  • all of these conditional formatting 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 five six seven
  • sheets maybe 50 rows per sheet I would
  • think that this this has some chance
  • some chance 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
  • I'll change you have to save the
  • workbook as xlsm change your macro
  • security record a macro to figure out
  • the code is up conditional formatting
  • for numbers and not equal to two that's
  • just a 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 and if statement to
  • prevent it from running on the title
  • sheet then a loop to check each form of
  • the cell add conditional formatting that
  • each cell that says if this cell dot
  • value is not equal to the value at the
  • time that it ran then we're gonna
  • highlight the things go back to excel
  • highlighted sign of shape out of shape
  • assign a macro to the shape click the
  • shape to run the macro and then I also
  • showed you the bonus tip dragging a VBA
  • module to a new workbook okay well I
  • think you're stalking by will see you
  • next time for another net cast from mr.
  • Excel

Download File

Download the sample file here: Podcast2059.xlsm

Title Photo: Bill Jelen / We Report Space