Macro that works on all highlighted cells

[jmpatrick]

I hope this makes some sense. I have a macro that works great after many headaches and revisions. I run it on a selected cell and it creates a hyperlink, formats the path properly, and names it based on the data in the selected cell. Here's the code:

Sub AddLink()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    Sheets("Hyperlinks").Visible = True
    Range(ActiveCell.Address).Name = "StartCell"
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Hyperlinks").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calendar").Select
    Application.Goto "StartCell"
    Dim newRange As Range
    Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))
    With ActiveSheet
   .Hyperlinks.Add Anchor:=newRange, _
      Address:=Sheets("Hyperlinks").Range("J1"), TextToDisplay:=ActiveCell.Text
      End With
    Sheets("Hyperlinks").Visible = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    Application.ScreenUpdating = True
End Sub

This works great for one cell at a time but time consuming if I have many rows that need to be run. I'd like to be able to highlight several cells that contain data and have my macro process them all. Seems like it would need to start at the the first selected cell, process it, then move down to the next cell and process, and so on. What if one of the selected cells is blank? Could it be skipped?

Thoughts?

 

[svendiamond]

You need a loop. Try something similar to this... just use this as a concept I didn't write the whole thing out. Also I'm not sure how you "highlighted" your cells but ColorIndex 6 is yellow. You can check the colorindex on a quick google search. But basically the idea is below:

Dim lastRow as Long, i as Long
With ActiveSheet
lastRow = .Cells(.Rows.Count,1).End(xlUp).Row

For i = 2 to lastRow

If .Cells(i, 1) <> "" and .Cells(i, 1).Interior.ColorIndex = 6 Then '(or whatever color you choose to highlight)
    ' the rest of your code goes here
End If

Next i

 

[DanteAmor]

I can make some improvements to your macro, but first you could explain a little what your macro does.

For example, in these lines you are copying cell J1, but then nowhere do you paste it.

    Range("J1").Select
    Application.CutCopyMode = False
    Selection.Copy

I understand that you are creating a hyperlink from the selected cell to cell J1, you can explain why you need this hyperlink. I ran your macro and then pressed the cell where the hyperlink is, but did nothing.

I await your comments and help you to make the macro work with a range of cells.

 

[jmpatrick]

For example, in these lines you are copying cell J1, but then nowhere do you paste it.

I have no idea what that J1 select and copy was for. I removed it and the macro still works.

 

[DanteAmor]

That's right, those lines were not necessary.
But there are other things in your macro that are not necessary, but before I improve your macro, you can explain what the purpose of the hyperlink is, and by the way, the hyperlink goes to a hidden sheet.

 

[jmpatrick]

The hyperlink leads to an MP3 audio file. You are correct. There is a hidden sheet (Hyperlinks) that forms the correct path to the MP3 by breaking down the data in the StartCell (which is a date). So, if a cell contains a date such as 5/29/1963 the macro will copy and paste that information into the Hyperlinks sheet. From there there are formulas that create the correct naming convention so that the hyperlink works properly. Make sense?

 

[jmpatrick]

All of that works great.

 

[DanteAmor]

if startcell has hyperlink with cell J1, will the next selected cell have hyperlink with cell J2 and so on?

 

[davesexcel]

Range(ActiveCell.Address).Name = "StartCell" '-where is the activecell starting?
Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols)) '- where do the row and column variables come from?

 

[DanteAmor]

if startcell has hyperlink with cell J1, will the next selected cell have hyperlink with cell J2 and so on?

Startcell copies and paste in cell A1, the next paste will be in cell A2 or will it always be A1?