Note: The other languages of the website are Google-translated. Back to English
Log in Register

 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?

Automatically run macro when cell formula result changes with VBA code

Automatically run macro when cell formula result changes with VBA code

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.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (8)
No ratings yet. Be the first to rate!
Varun
Varun
This comment was minimized by the moderator on the site
Thanks 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).
Thanks.
Reply
Tommy
Tommy
This comment was minimized by the moderator on the site
What'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.
Reply
Cenk
Cenk
This comment was minimized by the moderator on the site
So, what is my macro name. where can i find my macro name?
Reply
skyyang
skyyang     Cenk
This comment was minimized by the moderator on the site
Hello, 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

Sub 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
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
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"
Exit Sub
End If
On Error GoTo 0
Next
End Sub
Reply
Report
shan
shan
This comment was minimized by the moderator on the site
sorry 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
Reply
Pierre GYSELINX
Pierre GYSELINX
This comment was minimized by the moderator on the site
"Set Xrg = Range("C2:C8")
If Not Intersect(Xrg, Range("C2:C8")) Is Nothing Then"
this condition is ALWAYS encountered....
Make sense ???
Or is there something I didn't understand ?
Reply
Kerly
Kerly     Pierre GYSELINX
This comment was minimized by the moderator on the site
That right, did you solve this problem, and could you share for me, please?
Reply
Pierre-Yves Glidden
Pierre-Yves Glidden     Kerly
This comment was minimized by the moderator on the site
Hey guys,
I used the following code for this problem, hope it helps someone:

Private Sub Worksheet_Calculate()
Static oldValue
If Range("MyNamedRange") <> oldValue Then
CodeHere
oldValue = Range("MyNamedRange").Value
End If
End Sub
Reply
Report
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations