VBA run when cell selected in range containing specfic text not working

[p.huddy]

Hi,

I am trying to write a code then when someone clicks on a cell in Range G8:G1007 if the selected cell value is 'Copy' it copys the cell 2 columns along on the row that was selected but I can't work it out! Any pointers?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("G8:G1007")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
            If ActiveCell.Value = "Copy" Then
            ActiveCell.Offset(, 2).Copy
            End If
        End If
End Sub

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("G8:G1007")
    
    If Not Intersect(Target, KeyCells) Is Nothing Then
            If UCase(Target.Value) = "COPY" Then
            Target.Offset(, 2).Copy
            End If
        End If
End Sub

 

[p.huddy]

Perfect! Thank you!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("G8:G1007")) Is Nothing Then
   If UCase(Target.Value) = "COPY" Then Target.Offset(0, 2).Copy
End If
End Sub

 

[My Aswer Is This]

Not sure why you would want to do what your asking for.
Why not just double click on any cell in the range you want to copy?
This script will copy the cell in the range("I8:I1007")
Just by double clicking on the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("I8:I1007")) Is Nothing Then
Target.Copy
End If
End Sub