Worksheet Change
By Richard Rost

6 years ago

Change a Cell's Value when Another is Updated

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this Microsoft Excel TechHelp video, I will show you how to change the value in one cell when another cell is updated using VBA. This video came about because one of my students, Ronald, asked this question in the Excel Forum.

Members

There is no Extended Cut for this video.

Links

Excel Expert 11

Learn More!

Questions?

Please feel free to post your questions or comments below. Thanks.

Comments for Worksheet Change

Age	Subject	From
6 years	How does it sound?	Richard Rost

Subscribe to Worksheet Change
Get notifications when this page is updated

Intro

In this video, I answer a viewer's question about how to automatically update one cell based on changes made in another cell in Microsoft Excel. I will show you how to use the Worksheet_Change VBA event to create a macro that cumulatively adds values from an input cell to another cell each time the input changes. We'll go over setting up your worksheet, turning on the Developer toolbar, writing the VBA code in the Visual Basic Editor, and saving your file as a macro-enabled workbook.

Transcript

Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Today's question comes from Ronald. He asks, how do I change the value in one cell when another cell is modified? I want to add the value of cell 1 to cell 2 every time cell 1 is updated and not just something simple where you can say cell A1 equals A2. When A1 changes, A2 changes. He wants to literally take cell 1 every time it is updated and change cell 2 to add cell 1's value to whatever is in cell 2. So it is basically a cumulative total without the history.

In order to do that, we will need some VBA programming or a macro.

Today's question was posted in the forums by Ronald. Essentially, Ronald is putting together a sheet where he wants to type a value into one cell, and then as that value is changed in the future, it is a number. He wants to add another value to a different cell. So if he types in 5, then the other cell becomes 5. If he then types in 10, then the second cell becomes 15. If he types in another 10, then the other cell becomes 25.

I did not quite understand what he was asking at first because it seems counterintuitive; you will lose any history of what was entered in before. Just adding values to another cell is something you can do with a macro, but I do not know why you would want to. After going back and forth, that is exactly what he wants. He wants me to show him how to do that, so that is what we are going to do.

I am going to show you how to type a value into one cell and have it change the value in a different cell.

Here I am in a blank Excel sheet. I am going to put over here, let us say B2, a new value like that. Our new value will be typed right here into C2. I will just make that yellow and maybe put a box around it.

The sum of all the values that are entered into C2, one at a time, are going to drop right down here into, let us say, C6. I will make that one green.

As I type a value in here, here is what he wants to see happen. I type a 5 in there and then a 5 goes in there. If I come later and type in, let us say, a 3 up here, he wants this to change to 8. If you come up here now and type in 10, this one becomes 18. That is what he is trying to accomplish.

This is a pretty specific example for what Ronald's looking for, but this is also something that you could use to learn how to change values in different cells.

I am going to be covering examples like this in my forthcoming Excel Developer Level 1, which should be out in a couple of weeks. The first thing you have to do is turn your developer toolbar on. You normally do not see it. I cover how to do this in my last class, Excel Expert Level 11. You can find it on the website. I do cover the developer toolbar.

In a nutshell, it is under File, then Options. You have to go to Customize Ribbon and you will find the developer toolbar over here. Check it on. I talk about this in detail in Expert 11.

Let me get rid of these values that are in here right now. What we need to do is we need a macro that runs whenever the current worksheet is changed.

We are going to go into our Visual Basic Editor right here. We are going to bring up this guy, the VBA Editor. We will be talking about this in detail in the upcoming Excel Developer 1 class. Double-click on this thing right here. This is Sheet 1. That is the currently open sheet. We want to access the worksheet Change subroutine.

Come up here and pick Worksheet from this first list over here on the left. This brings in SelectionChange. Do not worry about that; we will talk about that later. Come over here, drop this down and pick Change.

Now we have the Private Sub Worksheet_Change. You can actually get rid of the SelectionChange.

Worksheet_Change fires or runs whenever any cell in your sheet is changed, and the target is what was changed, the range that was changed. Just to see what is happening here, type in this: MessageBox Target.Address. Show me the address of what target was changed - what cell was changed.

I am going to save this real quick. Book 2 is fine. I am not going to do much with this. Save it. That is fine.

Now, coming over here, just change any cell. Put a D in there. Hit Tab. There you go, F4. It tells you what cell was changed. If I delete it now, it will also tell you again that F4 was changed.

Come back to your editor. Now let us limit this to only notify us when that particular cell is changed. So I am going to say if target.Address equals, and what is a cell C2? But it wants it as an absolute. So $C$2. That is the cell that is edited.

Then pop up the value: target.Value. If you hit the dot here, you can see a list of all the different things you can get. There is Value, and then End If. So there is Address, and then there is Value.

This message here says that the following features cannot be saved in macro-free workbooks. I just hit Save on my keyboard, Ctrl S. Essentially, in order to save this macro in this workbook for use later, we have to save this as an XLSM macro-enabled file, which we will learn all about in Developer 1. But basically, when we save this, if you want to keep the macros, you have to do that.

We are just testing right now.

So now if I come over here, if I change any other cell, nothing happens. But if I click on this one here and put a 3 in there, now you can see it pops up a 3. So now I know how to see if this cell is changed and what the value is.

Now all I have to do is just add that to whatever is in C6. Let us go back over here.

So now, instead of message boxing the value, I want to add that value to whatever is in C6.

Here is what we are going to do. Let us get rid of the message box. The way that you change the value of another cell is with the range statement like this: Range. Then inside it, in quotes, C6, and you do not need to make these absolutes. He wants to keep adding the value to C6, so it equals Range("C6") + target.Value. You could put Range("C2") here if you want to. Either one works fine. Target.Value.

I am going to put target.Value because if, in the future, you decide to move this and change that, you do not have to change it in two places.

Save it. When prompted, save it as an XLSM to get rid of that error message. I will show you what I did in a minute, but let us make sure this works first.

Come over here. Put a 5 in there. Now you have a 5 down here. Come up here. Put a 2 in there. It added 2 to what was in there - 7. Come up here and type in 30. Enter. 37. If you keep adding values there, they will be added down below.

Why exactly he wants to do it this way, I am not sure, but you could, if you wanted to, have these values - when you type in like a 23 here - have it come over here to column M and start adding those values in a list down here, so you have some kind of a history, so it is tracking. I will cover that in a different lesson. I am not covering that today. We will talk about it maybe in Developer 1.

I do this with my bank spreadsheet that I have, where my bank sends me a balance every day in my email. I just copy the whole thing and paste it into Excel, and it parses it all out and puts the stuff in the next available row. I will teach you that eventually. It would be nice to keep some kind of a history here. You put a 14 in there, you add it to this value, and then you keep your history over here. But this is how he wanted to do it, so this was the TechHelp example.

I hope that shows you how to do it. You have to turn your developer toolbar on and then create your macro.

You have to save this spreadsheet. File, Save As. Because I already saved it earlier, I saved it as an XLSX, which is just a standard Excel sheet. If you go to File, Save As, pick XLSM macro-enabled over here, and then hit Save.

If it exists already, the difference is a normal Excel file is designed to be shared and sent around, and you can send it to your colleagues or put it online. They are macro-safe, so you cannot get anything malicious. Back in the old days, people used to put macro viruses in Word documents, Excel spreadsheets, and so on. Now, in order to do that, you have to save it as a macro-enabled sheet. So if you download or get sent by email a macro-enabled sheet, Excel knows to warn you and say, hey, there could be a virus in this. Are you sure you want to open it? Only open those from trusted sources, like me. Even when you do open it, it is going to say, macros have been disabled. Are you sure you want to enable them? You have to click enable.

Once you get past all that, there is the code for how this works, and we will talk about all that stuff in Excel Developer 1, coming up very soon.

Thanks for watching. I hope you learned something. Look for Excel Developer Level 1 coming soon to my website. I am sure you will see something on YouTube and Facebook and all the other places as soon as it is released. It is going to be a proper introduction to Excel VBA, unlike today, which was just a quickie to get you in the water.

Make sure you subscribe to my channel. You will get notified whenever I release a new video, and make sure you ring the bell and pick All for all notifications.

Ronald posted this question in my Excel forum. Be sure to head over to my website and check out the forum there. If you need help with Excel, you can drop a line to me right there on my TechHelp page, or you can email me directly.

Here is all the fun stuff - my blog, Facebook, Twitter, YouTube. Shameless advertising: watch my Excel Level 1 course for free on my website. There is the address right there. It is also on YouTube, but there is a quick link to it. If you like Level 1, Level 2, another entire hour-long video, is just $1.

Thanks for watching. Take care. We will see you next time.

Quiz

Q1. What is the main goal Ronald wants to accomplish in his Excel sheet?
A. To keep a history of all values entered into a cell
B. To add the value entered in one cell to another cell cumulatively every time the first cell is updated
C. To multiply two cell values together
D. To link two cells so they always show the same number

Q2. What method is required to achieve the cumulative sum as described in the video?
A. Regular cell formulas only
B. The Paste Special feature
C. VBA programming or a macro
D. Conditional formatting

Q3. In the demonstration, which cell is used for data entry?
A. B2
B. C2
C. C6
D. M2

Q4. Which cell in the example is used to display the cumulative total?
A. B2
B. C2
C. C6
D. M2

Q5. What specific Excel feature needs to be enabled before working with macros?
A. Data Analysis Toolpak
B. Conditional Formatting
C. Developer Toolbar
D. Formula Auditing

Q6. Where in Excel can you enable the Developer Toolbar?
A. File > Options > Customize Ribbon
B. Home > Format Cells
C. View > Macros
D. Data > Data Tools

Q7. In VBA, which event is used to trigger code when a cell is changed?
A. Worksheet_Activate
B. Worksheet_SelectionChange
C. Worksheet_Change
D. Workbook_Open

Q8. In the VBA example, what does the 'target' variable represent?
A. The cell displaying the total
B. The address of the worksheet
C. The cell or range that was changed
D. The macro name

Q9. Which code structure is used to reference and change another cell's value in VBA?
A. Cells.Add
B. Range("C6") = Range("C6") + target.Value
C. ActiveCell.Value = Selection.Value
D. Set Cell = Range("C2")

Q10. Why must the workbook be saved as an XLSM file?
A. So it can be emailed
B. To keep formatting
C. To allow macro code to be saved and run
D. To work on older versions of Excel

Q11. What happens if you try to save a macro in an XLSX file?
A. The macro is automatically converted to code
B. Excel warns you it cannot save the macro
C. The macro is saved but disabled
D. Nothing happens, it works fine

Q12. Why does Excel warn users when opening macro-enabled files?
A. To prevent accidental file deletion
B. Because macros might be slow to run
C. Macros can contain malicious code
D. To remind users to save their work

Q13. What does the Worksheet_Change event allow you to do?
A. Automatically save the workbook after changes
B. Run specific code whenever any cell changes in the worksheet
C. Lock the worksheet from further changes
D. Format cells based on their value

Q14. What was suggested as a possible enhancement not covered in this video?
A. Formatting cells based on values
B. Sending an email automatically after entry
C. Creating a history of all entered values
D. Hiding columns automatically

Q15. What should you do before enabling macros in a file received from someone else?
A. Enable without any hesitation
B. Only enable from trusted sources
C. Enable after checking the internet
D. Enable after saving a backup

Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-A; 7-C; 8-C; 9-B; 10-C; 11-B; 12-C; 13-B; 14-C; 15-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone addresses a question that many Excel users have at some point. I am Richard Rost, your instructor, and our topic today comes from Ronald, who wanted to know how to update the value in one cell based on changes made to another cell, specifically in a cumulative way.

Ronald's scenario is a bit different from the usual Excel formulas. He wants to set up a sheet where, whenever he enters a number in one cell (for example, C2), that number gets added to another cell (such as C6). So, if he types 5 in C2, C6 displays 5. If he enters 10 in C2 next, C6 updates to 15, and so on. Each new entry in C2 increases the running total in C6, but the individual entries are not saved anywhere else. This method keeps a cumulative total but without maintaining any history of what was entered previously.

My initial reaction was that this approach would erase all previous entries, essentially losing important data that might be useful for auditing or future calculations. Despite my concerns, Ronald confirmed that this is exactly what he wants for his project. So, my goal is to show how to make that work in Excel using VBA.

This type of operation is not possible with traditional formulas since you would need to capture each change and add it to an existing value elsewhere. To accomplish this, you need to use a macro, specifically by writing VBA code that responds to worksheet changes.

If you want to follow along, start with a blank Excel sheet. Set up a spot for entering new values, say cell C2 (you might want to color it yellow to help it stand out), and decide where you want your total to accumulate, like C6 (highlighting it green can help for reference).

To make this work, you first need to enable the Developer toolbar in Excel, since it is not available by default. To activate it, go to the File menu, then Options, and choose Customize Ribbon. From there, check the box for Developer. I cover this process in detail in my Excel Expert Level 11 class if you need additional help.

With the Developer tab enabled, you can open the Visual Basic Editor. Within the VBA Editor, you will work with the code sheet for the specific worksheet where you want this behavior. Double-clicking on the relevant sheet brings up the code window. Here, you want to use the Worksheet_Change event procedure, which executes code whenever something on the worksheet is changed.

The Worksheet_Change routine receives a parameter called Target, which tells you which cell was modified. For testing purposes, you can display a message showing which cell triggered the event by checking Target.Address, but eventually, you will want your code to respond only when a specific cell is changed — in this case, C2. If Target.Address equals $C$2, that means the change happened in the right spot.

The next step is to take the new value entered in C2 and add it to whatever is currently in C6. This involves reading the value from C6, adding the value from C2 (Target.Value), and saving the result back into C6. This approach ensures that every new value entered in C2 will be added to the existing total in C6.

Before saving your workbook, be aware that Excel will prompt you to save as a macro-enabled worksheet (with an XLSM file extension) whenever you include macros. Standard XLSX files cannot store macros for security reasons. Macro-enabled files ensure that Excel treats the sheet with caution and warns users about potentially unsafe code. Always make sure you save your workbook with the proper format if you intend to use or share macros.

Once the macro is working, entering any numeric value in C2 will add that value to the sum in C6. For example, type 5 in C2 and C6 becomes 5, type 2 next and C6 is now 7, enter 30 and C6 moves up to 37, and so on.

While this solution meets Ronald's needs, I would usually recommend using a method that also records each entry as a history log for better tracking. I use a similar method with my own bank records, where each new amount gets recorded in the next available row, so nothing is lost. That is a more robust solution and one I teach in detail in my other courses, such as Excel Developer Level 1.

If you want to use this macro functionality, do not forget to enable macros after opening a macro-enabled workbook. Excel may disable macros by default to protect you from potential security threats, so only enable them when you trust the file's source.

Excel Developer Level 1 will be available soon on my website and will provide a detailed introduction to programming Excel with VBA. Today's video was a quick illustration of a specific question, but if you want to learn more about automating tasks in Excel, my courses will be a great resource.

Make sure to subscribe to my channel for updates, and visit my website for the forum where you can ask questions like Ronald did, or email me directly if you need help.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Setting up cells for value entry and cumulative total

Enabling the Developer toolbar in Excel

Opening the Visual Basic Editor

Locating and using the Worksheet_Change event

Identifying the changed cell with Target.Address

Limiting the macro to respond to a specific cell (C2)

Adding the value from one cell to another cell using VBA

Preventing macros from running on all cell changes

Saving a workbook as an XLSM macro-enabled file

Using VBA code to automate cell calculations

Testing the macro to verify cumulative totals