Identify cell values in the same row as the row that triggered the macro

Question

I want to get cell values from specific cells in the same row as the one triggered my macro.

I've managed to crate a macro that finds cell value from specific cells depending on what the triggering cells value is. Below code tells the macro if cell value of cell B5 is 1 then copy the text in A8, A5 and A2 into specific areas in the mail text. All the ZZZZZ is predetermined phrases in the mail.

What i want is, that if I put in 1 in any row in column K, it will take the cell value from column C, F and G in the same row as I put the 1 in and put it where A8, A5 and A2 is inserted in the mail. If I were to put 2 in B5 the macro chooses A9 instead of A8. But that code is not pasted below but is identical. What I want is for the code to be somewhat similar but know what to write instead of B5, A8 etc.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    'If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("B5"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = 1 Then
        Call Mail_small_Text_Outlook1
    End If
__________________________________________________________________________
Sub Mail_small_Text_Outlook1()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "ZZZZZZZZZ," & vbNewLine & vbNewLine & _
                "ZZZZZZZZZZZZZZZZZZ: " & Range("A8").Value & vbNewLine & _
                "  " & vbNewLine & _
                "ZZZZZZZZZZ: " & Range("A5").Value & vbNewLine & vbNewLine & vbNewLine & _
                "ZZZZZZZZZ" & vbNewLine & _
                "ZZZZZZZZZ"
    On Error Resume Next
    With xOutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "ZZZZZ - " & Range("A2").Value
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Use Target.Row to get the row number. Then you can pair that with the columns you want. — BigBen

SJR SJR · Accepted Answer · 2019-11-08T13:35:50

You can reference the Row property of the changed cell, Target, thus:

if target.column=11 then  'k
   if target.value=1 then
     cells(target.row,"C") '...whatever
     cells(target.row,"F") '...whatever
     cells(target.row,"G") '...whatever
   end if
end if

Identify cell values in the same row as the row that triggered the macro

1 Answers