|Let's introduce the concept of "volitile functions".|
The following was stolen without permission from:
Volatile and Non-Volatile Functions
Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.
Since TODAY() is a volitile function, it is going to recalculate every time the sheet opens, closes, recalcualtes, etc. Try this: Create a new workbook with nothing but an =TODAY() function in a cell. Save it, open it and try to close it. Excel will ask you if you want to Save the Changes, even though you didn't change anything.
However, volitile functions do not trigger change event macros, which is why you are not seeing code run.
(A side note...I need to come up with a new phrase to replace "fire the event". In reality, the Change event "fires" with every change a user makes to the worksheet, but in many (most?) cases the first line checks to see what the change was and either runs the rest of the code or doesn't. For example, this code will "fire" whenever the worksheet changes, but it will only execute the loop section if the change was made to A1. Note the "If Target.Address" instruction.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" then
For rw = 3 To 3000
If Range("B" & rw) < Range("F1") Then
Range("C" & rw) = Range("C" & rw).Value
End side note...)
So, how do we get the code to run when the workbook opens?
How about using the Open event instead?
- Right click the sheet tab and delete the Change event code.
- In the VBA editor, assuming you have the Project Explorer pane showing, (View...Project Explorer) you should see a listing for ThisWorkbook.
- Double Click ThisWorkbook and a new editor pane will open.
- Click the Drop Down next to General and choose Workbook.
The framework for a Workbook_Open macro should appear.
Highlight the text and paste the code below in it's place. This code will run everytime the workbook is opened. I tested it by changing my system date to "tomorrow" (11/24) and then opening the workbook and it changed all the cells next to today's date (11/23) or earlier.
Private Sub Workbook_Open()
For rw = 3 To 3000
If Sheets(1).Range("B" & rw) < Sheets(1).Range("F1") Then
Sheets(1).Range("C" & rw) = Sheets(1).Range("C" & rw).Value
One more note:
The code is going to check the date in each cell in the range B1:B3000 and execute the .Value line regardless of whether there is a formula in the cell or not. In other words, it's going to "change" cells in Column C that it has previously changed, but since there is already a Value in the cell, nothing will happen.
The only way to prevent this "change" is to check for the date and then check for a formula. In terms of efficiency, I'm not sure that there is much difference between checking for a formula before making a change and just executing the change line even if it's not needed.