How to automatically run macro when cell formula result changes?
Supposing, I have a list of formulas based on the data in column A and column B as following screenshot shown, now, I want to run a specific macro code automatically when formula result changes with its relative cells change. Is there any good idea to solve this job in Excel?
The following VBA code can help you to execute a specific code automatically when the formula result cell changes, please do as this:
1. Right-click the sheet tab that you want to use, and then choose View Code from the context menu, in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module:
VBA code: Automatically run macro when cell formula result changes:
Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("C2:C8") If Not Intersect(Xrg, Range("C2:C8")) Is Nothing Then Macro1 End If End Sub
Note: In the above code, C2:C8 is the range of formula cells that you want to use，maco1 is the name of the macro you want to run automatically. Please change it to your need.
2. Then save and close this code window, now, when the data in range A2:B8 changes to cause the formula result changes, your specific macro code is triggered at once.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months agosorry it dosn't work it works if i put data manually. but i want to work it automatically bcz my data is updating by rand calcaulate
To post as a guest, your comment is unpublished.· 1 years agoSo, what is my macro name. where can i find my macro name?
To post as a guest, your comment is unpublished.· 1 years agoHello, Cenk,
The macro name is the macro code you have inserted into the Excel file, and you just need to change the Macro1 in the above code to your own.
For example, i insert a code here, and the macro name is: ColorCompanyDuplicates
'Updateby Extendoffice 20160704
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xChar As String
Dim xCellPre As Range
Dim xCIndex As Long
Dim xCol As Collection
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
xTxt = ActiveSheet.UsedRange.AddressLocal
Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
xCIndex = 2
Set xCol = New Collection
For Each xCell In xRg
On Error Resume Next
xCol.Add xCell, xCell.Text
If Err.Number = 457 Then
xCIndex = xCIndex + 1
Set xCellPre = xCol(xCell.Text)
If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex
xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex
ElseIf Err.Number = 9 Then
MsgBox "Too many duplicate companies!", vbCritical, "Kutools for Excel"
On Error GoTo 0
To post as a guest, your comment is unpublished.· 1 years agoWhat's the point of the condition? It'll always return true...in other words: it'll run without it. This also runs whenever any cells on the sheet change value.
To post as a guest, your comment is unpublished.· 2 years agoThanks a lot! This really helped me out.
Is there any method to retrieve the address of the changed cell (with formula i.e Column C in this example).