VBA to Copy and Paste values to same row range in same sheet based on a Trigger

[RaginMuse]

I'm fairly new to VBA and I'm hoping I could get some help here to achieve what I'm trying to do. I've searched and read through, and a lot of solutions are about copying and pasting from one sheet to another.
Basically, I want to Copy and Paste the Values in the same row range based on a trigger from another column in the same Sheet.

I've Column G with the trigger word, which is "Hired"; I've data range J5:AG436 For example, if G11 is "Hired", I want the existing data in J11:AG11 to be copied and pasted as Values.

Here's a code that I'm trying to use but got nowhere with it. Perhaps this is not it?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim triggerColumn As Long
triggerColumn = 7 ' column G is the trigger column
Dim triggerWord As String
triggerWord = "Hired" ' the trigger word to look for
   
'define range to which the code will be applied
Dim dataRange As Range
Set dataRange = Me.Range("J11:AG436") 'change to match the range of your data
   
If Not Intersect(Target, dataRange) Is Nothing Then
' a cell within the data range was changed
If Target.Column = triggerColumn Then
' the trigger column was changed
If InStr(1, Target.Value, triggerWord, vbTextCompare) > 0 Then
' the trigger word was found in the changed cell
' copy and paste special values from the current cell
                Target.Copy
                Target.PasteSpecial xlPasteValues
End If
End If
End If
End Sub

Any help is greatly appreciated.

 

[RaginMuse]

Hello RaginMuse,

The code amended as follows with a message box pop-up might suffice. You can change the actual message to suit your needs if you like.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim Warning As String
   
    If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    Warning = MsgBox("Be warned that the action you are about to take is irreversible. To continue, click OK.", vbCritical + vbOKCancel, "WARNING")
          If Warning = vbOK Then
                trow = Target.Row
                If Target.Value = "Hired" Then
                     With Me.Range(Cells(trow, "J"), Cells(trow, "AG"))
                            .Value = .Value
                     End With
                     Else: Exit Sub
                End If
          End If
         
    Application.EnableEvents = True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Thank you very much!!!

 

[vcoolio]

You're welcome RM. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,
vcoolio.