Loop URL links through range

[Sahak]

Hi All,

I have big list of URL links having jpg pictures.

I would like a VBA code, which will loop trough rows, and if the link does not have a picture, then color that cell yellow, if there is a picture, then go to check next cell (No need to open the picture)

Here are some links for example

http://www.abcdefg.com/albums/MG407B.jpg

http://www.abcdefg.com/albums/MG407M.jpg

http://www.abcdefg.com/albums/MG228A.jpg

http://www.abcdefg.com/albums/MG105N.jpg

http://www.abcdefg.com/albums/MG366A.jpg

etc. Thank you in advance

 

[PeteWright]

Is this what you want:

Book1.xlsm
	A
1	http://www.abcdefg.com/albums/page1.html
2	http://www.abcdefg.com/albums/MG407M.jpg
3	http://www.abcdefg.com/albums/page2.html
4	http://www.abcdefg.com/albums/page3.html
5	http://www.abcdefg.com/albums/MG366A.jpg
6	http://www.abcdefg.com/albums/page4.html
7	http://www.abcdefg.com/albums/MG407B.jpg
8	http://www.abcdefg.com/albums/page5.html
9	http://www.abcdefg.com/albums/page6.html
10	http://www.abcdefg.com/albums/page7.html
11	http://www.abcdefg.com/albums/MG228A.jpg
12	http://www.abcdefg.com/albums/MG105N.jpg
Sheet1

Sub HighlightURLs()
   Dim DataCell As Range
   Dim Data As Range
   Set Data = ActiveSheet.Range("A1:A12") '<-- user defined Range
   
   For Each DataCell In Data
      If Right(DataCell.Value2, 3) <> "jpg" Then DataCell.Interior.Color = RGB(255, 255, 0)
   Next DataCell
End Sub

 

[Sahak]

Thank you very much for repy. Unfortunaly this not what I want.
I apologize for not explaining in detail. The point is that all the link list is prepared in advance, and all of them have .jpg at the end, and not all pictures put on the web, we put them partialy, that is the reasun why we want to know which link has picture, which link does not.

 

[PeteWright]

Okay, but how do you determine, what is a picture and what is not a picture?

The VBA "program" can only do, what a human would do, but automated.

So how would you sort out a link without a picture? What would you do?

 

[Sahak]

The URL address is one only; it is the same for all pictures, in my example: http://www.abcdefg.com/albums/
We add a picture with extension “.jpg” in folder “albums” with picture name, for example “MG407B”, after that, if we click on link “http://www.abcdefg.com/albums/MG407B.jpg” on Excel worksheet, we will see that existing picture, but if we click on link “http://www.abcdefg.com/albums/MG105N.jpg”, it will give an error, because there is no picture named “MG105N.jpg” in folder.
Because we have too many pictures, it is more convenient and much easier (copy, paste …) to put the URL addresses on Excel worksheet first, than to put the picture in a folder, and then put the URL addresses on Excel worksheet.

 

[PeteWright]

Here is another option. Try the below code and change the Data Range to suit your needs: Set Data = ActiveSheet.Range("A1:A2")

Book1.xlsm
	A
1	http://www.abcdefg.com/albums/MG407B.jpg
2	https://picsum.photos/id/237/536/354.jpg
Sheet1

Option Explicit

Sub HighlightURLs()
   Dim DataCell As Range
   Dim Data As Range
   Set Data = ActiveSheet.Range("A1:A2") '<-- user defined Range
   
   For Each DataCell In Data
      If HttpResponseOk(DataCell.Value2) Then DataCell.Interior.Color = RGB(255, 255, 0)
   Next DataCell
End Sub

Private Function HttpResponseOk(URL As String) As Boolean
   Dim request As Object
   Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
   On Error Resume Next
   request.Open "HEAD", URL
   request.send
   If request.Status = 200 Then
      HttpResponseOk = True
   Else
      HttpResponseOk = False
   End If
End Function

 

[Sahak]

Yes, this is what I wanted.
Thank you very much, and God bless you.

 

[PeteWright]

Glad I could help. Thanks for the feedback.
If you encounter any problems don't hesitate to ask.

Please mark the answer as solution if it's exactly what you needed