Hello,

I currently have the following macro that converts text to a hyperlink.

Sub CovertTxt2Hyperlink()
Dim cCell As Range
Dim strHLinkBase As String

strHLinkBase =
"http://grow.hp.com/Saba/loginAsUser.jsp?deepLinkName=CourseDetail&deepLink Params=courseId="

For Each cCell In Selection.Cells

If cCell.Hyperlinks.Count = 0 Then
On Error Resume Next
ActiveSheet.Hyperlinks.Add _
Anchor:=cCell, _
Address:=strHLinkBase & cCell.Value, _
TextToDisplay:=cCell.Value
cCell.NumberFormat = "@" 'Format cell as text
End If
Next cCell
End Sub

It works great for the spreadsheets that I have used it on to date. However,
I have received a new workbook that I will be receiving quarterly that will
require modification to this macro to further automate the process of
converting the cell content to a hyperlink.

In this workbook I have 4 sheets; Welcome, Sales, Consulting & Suport. In
the last 3 sheets there is a table that has a catalog id column beginning at
D9. In the current macro I have to select the cells and then run the macro to
convert the contents. I want to change the macro so that all of the cells in
Column D, beginning at Row 9 in the Sales, Consulting & Support sheets will
be converted when the macro is run.

In addition to this change the macro needs to take several different Catalog
id formats into consideration and apply a specific action.

1) Currently this macro works fine if the format is either; 1234, 12345,
00012345, 00123456 or 01234567. In the new macro I want the hyperlink to be
the same as what it currently is written to create if it finds 1 of the 5
noted id formats.

2) Another format that is found in this column is a url. If the macro finds
this format I need the url to be retained in the hyperlink.

3) The 3rd format is an id that begins with WBT. In this case I need the
hyperlink to contain, http://grow.hp.com/Saba/Web/Main
Additionally, in this case an additional enhancement would be to add a
Comment to the cell with instructions on what to do once they are redirected
to the url.

4) The final format is that the cell may contain the word Exam. In this case
I don't want any hyperlink to be created.

Any assistance in whole or in part would be greatly appreciated. Please let
me know if any additional information is required.

--
Regards,
Chris