Macro code to show images in excel comments

[excel_2009]

Hi everyone,

I have some coding that displays images within a comment box when the macro is ran, the image urls are in column H.

The coding is as follows and works perfectly fine, however it needs a modification which I cannot figure out.

I need the coding to check the whole of column H and only create the image comment IF there is a jpg in the cell. i.e www.google.com/image/test1.jpg would have the image generated in the comment whereas this value Hello testing would not i.e. the macro should skip cells that are blank and cells that do not contain the value jpg.

Here is the current coding, any help would be greatly appreciated:

Sub image()


Dim rng As Range, cell As Range
With ActiveSheet
If Range("H2").Value <> "" Then
Set rng = .Range(.Range("H2"), .Range("H2").End(xlDown))
ElseIf Range("H2") <> "" Then
Set rng = .Range("H2")
Else
End
End If
End With


For Each cell In rng
cell.Select
On Error Resume Next
If Selection.Value <> "" Then
Selection.AddComment
Selection.Comment.Visible = False
Selection.Comment.Text Text:=""
Selection.Comment.Visible = True
Selection.Comment.Shape.Select True


Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 250
Selection.ShapeRange.Width = 250


Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.UserPicture ActiveCell.Value
ActiveCell.Comment.Visible = False
Else
End
End If
Next
End Sub

Thank you

 

[John_w]

Try incorporating this statement:

If Right(cell.value,4) = ".jpg" Then

 

[excel_2009]

Hi John,

I have tried incorporating the suggestion however once doing so the coding stops at H2?

Sub image()


Dim rng As Range, cell As Range
With ActiveSheet




If Range("H2").Value = ".jpg" Then
Set rng = .Range(.Range("H2"), .Range("H2").End(xlDown))
ElseIf Range("H2") <> "" Then
Set rng = .Range("H2")
Else
End
End If
End With


For Each cell In rng
cell.Select
On Error Resume Next
If Selection.Value <> "" Then
Selection.AddComment
Selection.Comment.Visible = False
Selection.Comment.Text Text:=""
Selection.Comment.Visible = True
Selection.Comment.Shape.Select True


Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 250
Selection.ShapeRange.Width = 250


Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.UserPicture ActiveCell.Value
ActiveCell.Comment.Visible = False
Else
End
End If
Next
End Sub

Thank you