RyanDev.com

Many people use Microsoft Excel to aggregate data from many sources or to analyze the data using Excel’s many great functions.  Wouldn’t it be great to be able to trigger work-flow or notifications if certain cells changed or maybe if they changed above a certain threshold?  This is easy to do.

First, open the workbook that contains the cells you would like to monitor.  Then press Ctrl+F11 to open the Visual Basic Code Editor.  You first need to tell Excel that you would like to be notified when cells on a particular sheet change.  You can do that with the following code.

---

Sub auto_open()
‘if you make any changes to the code, you will need to save and reopen the workbook
ThisWorkbook.Worksheets(”Sheet1″).OnEntry = “DidCellsChange”
End Sub

---

You have now wired the OnEntry event on the Sheet object to your code.  This method is called each time a cell changes.Now, all you have to do is have a Sub routine called “DidCellsChange” to catch the event.  The below example shows how to check to make sure that the cell being changed is within a range of cells which you care about.

---

Sub DidCellsChange() ‘checks to see if the cells in the Range have changed
If Not Application.Intersect(ActiveCell, Range(”b5:b65″)) Is Nothing Then
  ‘ do your code here
  ‘ call out to a webservice or change the cell color or send an email, etc.
End If
End Sub

---

Pretty simple.  Note, your workbook will have to be open for this to work because it is dependent on Excel’s formulas and features.