How to simplify Target.Address?

Hi everyone,

I am following this forum for long time and found many times solution.
Now is time for my question. I could not find answer anywhere.

I have got list of products in column DK and pictures names in column DL (where picture name is =DK&".jpg")
All the pictures are stored in P:\General\GADD\PICTURES\

Pictures are displaying in comment window.
Everything is working great, but I have got more than 400 lines of products.

How simplify my code?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    
        If Target.Address = "DK2" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL2").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK3" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL3").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK4" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL4").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK5" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL5").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK6" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL6").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK7" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL7").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK8" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL8").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK9" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL9").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK10" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL10").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK11" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL11").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
        If Target.Address = "DK12" Then
    NewPic = "P:\General\GADD\PICTURES\" & Range("DL12").Value
    Target.Comment.Shape.Fill.UserPicture NewPic
    End If
    
End Sub

Thanks for any reply

Hello londonpeter,

Welcome to the Forum!

Here is the condensed version...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim NewPic As String
    
        If Not Intersect(Target, Range("DK2:DK12")) Is Nothing Then
                NewPic = "P:\General\GADD\PICTURES\" & Target.Offset(0, 1).Value
                Target.Comment.Shape.Fill.UserPicture NewPic
        End If
        
End Sub

Hi londonpeter
This is untested but you could try something like this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 115 Then
        NewPic = "P:\General\GADD\PICTURES\" & Target.Offset(0, 1).Value
        Target.Comment.Shape.Fill.UserPicture NewPic
    End If
End Sub

Try:

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ProductList     As Range, _
        NewPic          As String
        
    Set ProductList = Range("DK1:DK" & Cells(Rows.Count, "DK").End(xlUp).Row)
    
    If Not Intersect(Target, ProductList) Is Nothing Then
        NewPic = "P:\General\GADD\PICTURES\" & Target.Offset(0, 1).Value
        Target.Comment.Shape.Fill.UserPicture (NewPic)
        
    End If

 End Sub

Note: this is a duplicate of an identical post on mrexcel with similar solutions!

The answers here look similar to those at http://www.mrexcel.com/forum/showthread.php?t=630275