Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Hyperlink (Excel 2002)

    I have a workbook that contains 2 sheets. The Calendar Sheet which generates a 12 month calendar by just inputting a start date on cell B2. The 2nd sheet (Day List Sheet) will also generate the same year calendar in one long column. What I want to do is scan each cell in a range on the Calendar Sheet particularly the B4:X54 range and if the cell is shaded in red create a hyperlink from the shaded cell to a cell on the Day List Sheet two cell to the right corresponding to the same date.

    I've created a name range called RangeToScan corresponding to the B4:X54 range. Is there a simple way to automate creating these hyperlinks? Similar to the links I've created on the first month of the calendar. I am also attaching the worksheet. Thank you in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Hyperlink (Excel 2002)

    Does this macro do what you want?

    <pre>Option Explicit
    Sub CreateHyperlink()
    Dim rCell As Range
    Dim rng As Range
    Dim rLookup As Range
    Dim lRow As Long
    Dim AWF As WorksheetFunction
    Dim iDate As Integer

    Set rng = Worksheets("Calendar").Range("B4:X54")
    Set rLookup = Worksheets("Day List").Range("B:B")
    Set AWF = Application.WorksheetFunction

    For Each rCell In rng
    If rCell.Interior.Color = vbRed Then
    lRow = 0
    On Error Resume Next
    lRow = AWF.Match(rCell.Value * 1, rLookup, 0)
    On Error GoTo 0
    If lRow <> 0 Then
    rCell.Select
    rng.Parent.Hyperlinks.Add Anchor:=rCell, _
    Address:="", _
    SubAddress:="'" & rLookup.Parent.Name & _
    "'!" & rLookup.Cells(lRow).Offset(0, 2).Address
    End If
    End If
    Next
    Set rCell = Nothing
    Set rng = Nothing
    Set rLookup = Nothing
    Set AWF = Nothing
    End Sub</pre>


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Hyperlink (Excel 2002)

    This is perfect Steve. A huge thanks as always.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Hyperlink (Excel 2002)

    Hi Steve,

    I just want to compliment you on this code. When Jolas posted this query I immediately jumped in and started attempting some code. I was eventually stumped with how to assign the hyperlink to the correct cell in the datelist on the second sheet! I have recently returned to this post to review the solution and your code came as a pleasant surprize. It turned out to simply using the match function to search for a match. (I didn't even think of this at the time!) It certainly shows that a person should keep a very open mind when conjuring up solutions....and not to always think that you should "invent something new!"

    Cheers for the solution.
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Hyperlink (Excel 2002)

    Thanks for the compliment.

    One thing to be aware of when using MATCH (as well as HLOOKUP and VLOOKUP) in VB is that will generate an error if a match is not found. Excel "traps the error" implicitly and reports it (#NA error). In VB, however, this leads to a runtime error so it must be explicilty trapped (or ignored as I have done in the code). Notice the resetting of the variable which holds the "lookup" found to zero so if no match is found, it gives a zero to indicate that no match was found.

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Hyperlink (Excel 2002)

    I noticed that. Thanx for the explanation!
    Cheers
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •