CODEWITHSUNDEEP
excelvba
humantorch47
humantorch47

Reputation: 3

How to run macro in excel only on the recently updated cells

I have macro which sends email when I enter Reject on particular cells.

I have added this so that that macro runs whenever something changes in that particular column

  Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UpdatedCells As Range
    Set UpdatedCells = Intersect(Me.Range("G3:G60"), Target)

    If Not UpdatedCells Is Nothing Then
        PSEC UpdatedCells
    End If
End Sub

Sub PSEC(ByVal UpdatedCells As Range)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In UpdatedCells
       If cell = "rejected" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "xxxx"
                .Body = "xxx"
                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

The problem is, it sends email to previously updated cells also. Is there a way to run macro only on the recently modified cells?

Upvotes: 0

Views: 56

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

First you need to get the intersection with your target set to a variable UpdatedCells so you know which cells in the desired range were updated.

Those UpdatedCells then need to be passed to your procedure PSEC so you can process all the updated cells.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UpdatedCells As Range
    Set UpdatedCells = Intersect(Me.Range("G3:G60"), Target)

    If Not UpdatedCells Is Nothing Then
        PSEC UpdatedCells
    End If
End Sub

Your procedure PSEC needs to be able to receive a parameter, so you can loop through all changed cells and process the data in that rows to send an email.

Public Sub PSEC(ByVal UpdatedCells As Range)
    Dim Cell As Range
    For Each Cell In UpdatedCells
        If Cell = "Rejected" Then
            ' send your email here!

            Debug.Print ThisWorkbook.Worksheets("Feedback").Cells(Cell.Row, "B").Value
            ' This will print the value of column B of the recent changed cell (just as an example how to access data in this row
        End If
    Next Cell
End Sub

Upvotes: 1

Related Questions