Hyperlink covers several cells, how to make it linkable from any of

[xenios]

Hello.
I have a link in cell A5, but it covers another 3-4 rows.
How to merge the columns, so it would work clicking only part of the expression?

Below are initial

Initial vba for the link creation if it helps. I have 3 of them of different type. and in attachment how it looks at the moment.
Here is the 1st one.
Sub Hyper()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "HOTELS" Then
ws.Hyperlinks.Add _
Anchor:=ws.Range("B5"), _
Address:="", _
SubAddress:="HOTELS!A1", _
TextToDisplay:="Back To The Hotel List"
End If
Next ws
MsgBox ("Completed")
End Sub

 

[xenios]

Tried to use the following code, Bit it works only on the sheet I'm on, but not for the whole workbook.

Sub merge()
Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
Range("A5:D5").merge

Next WS
MsgBox ("Completed")
End Sub

 

[MARK858]

Needs to be

WS.Range("A5:D5").merge

 

[xenios]

Needs to be

WS.Range("A5:D5").merge

Cool, thank you!!

Is there a way to tell it to merge only, when the text in A5 is "Back to The Hotel List", as I'm adding more pages that are different?
And if it's possible for it to merge cells with the same text (To The Top) when I know specific column, but the row varies?

 

[MARK858]

There is but I am loathe to code it for you as merged cells are likely to cause you issues in the future.
Have you looked at using Center across Selection?

How to use Center Across Selection in Excel

In this video we show you how to use a lesser-known alignment option called Center Across Selection to center text without changing the worksheet grid.

exceljet.net

 

[xenios]

There is but I am loathe to code it for you as merged cells are likely to cause you issues in the future.
Have you looked at using Center across Selection?

How to use Center Across Selection in Excel

In this video we show you how to use a lesser-known alignment option called Center Across Selection to center text without changing the worksheet grid.

exceljet.net

Thank you for the tip, very useful.
But unfortunately still as a link it works only when you click on the 1st cell

 

[MARK858]

I can't believe that I have got myself involved in a post about merged cells but here goes...

Your first part is easy, it is just

Sub merge()
    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Range("A5").Value = "Back to The Hotel List" Then WS.Range("A5:D5").merge
    Next WS
    MsgBox ("Completed")
End Sub

As for the 2nd part can you describe in more detail what you want...

Are you just looking down the column, finding one instance of "To The Top" and merging to column D of that row?
Are you doing the same as the above but with multiple instances of "To The Top"?
Or something else?

 

[xenios]

Sorry for involving you in this

Here is the link how it looks, it's the same as above, but I don't have specific row, there is the problem.

Book1.xlsm

drive.google.com

"Back to The Hotel List" is always in column A.
"To the top" is always in column U

 

[xenios]

I can't believe that I have got myself involved in a post about merged cells but here goes...

Your first part is easy, it is just

Sub merge()
    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Range("A5").Value = "Back to The Hotel List" Then WS.Range("A5:D5").merge
    Next WS
    MsgBox ("Completed")
End Sub

As for the 2nd part can you describe in more detail what you want...

Are you just looking down the column, finding one instance of "To The Top" and merging to column D of that row?
Are you doing the same as the above but with multiple instances of "To The Top"?
Or something else?

Somehow, it's not working, no errors, but things stay the same

 

[MARK858]

Sorry for involving you in this

Not your fault, it's mine. We choose what we get involved in ?

Somehow, it's not working, no errors, but things stay the same

Then you just have an hyperlink.
Turn on the macro recorder and carry out the actions in the section "Find All Hyperlinks Linked to Specific Text in Excel" in the link below

How to Quickly Find Hyperlinks in Excel (using Find and Replace)

In this tutorial, you'll learn how to use Find and Replace to quickly find hyperlinks in Excel. You can find all the hyperlinks or the ones that contain a specific text.

trumpexcel.com

Then at the bottom of the code add the lines

    Dim Findrange As Range
    Set Findrange = Columns("A:A").Find("Back to The Hotel List", , xlValues, xlWhole, xlByRows, xlNext, False)
    If Not Findrange Is Nothing Then Findrange.Resize(, 4).merge

and test. Remember when you put it in your loop you need to add the WS. to the range.

Then do the same with the other word, changing the word and the column in the code (make sure that your wording in the find is correct as your image just shows "Top" not "To the Top").