Is anyone kind enough (clever enough?) to help me change the following code which I downloaded from this forum a few weeks ago.
I have tried experimenting, but as I have no training as a programmer I am getting it wrong.
Private Sub Worksheet_Activate() Dim wSheet As Worksheet Dim l As Long l = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name <> Me.Name Then l = l + 1 With wSheet .Range("A1").Name = "Start" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _ SubAddress:="Index", TextToDisplay:="[Back-to-Index]" End With Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _ SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub
This super, short code creates an index page of all the worksheets within a workbook
 One each existing workbook
I would like the code to format the [Back to Index] text that it puts in cell A1 on each sheet. For example to
Align vertical = centre
Align horizontal = centre
Font Sixe = 8
Backgroung colour = beige
text colour = blue
 The current index page lists all worksheets in column A
I would like the code to format the cells. For Example
font size = 18
column width = 25
It would be just spiffing if I could get the cell to toggle/change background colour then the mouse cursor went over it
And the one I have really struggled with ...
I would like the code to add new data in columns B & C.
* each time the code puts a worksheet name/link into column A
* I would like the code to move to column B and input a formula which picks up the contents of cell B2 from the worksheet which it has just listed in column A
* then I would like the code to move to column C and input a formula which picks up the contents of cell B3 from the worksheet which it has just listed in column A
The idea is to build a index page which picks up title and description information from eah of the worksheest in the workbook.
I think this would make a really neat index page, and I am hoping that someone clever thinks so too
Thanks for your attention