I could not to find the best thread for my issue anywhere.
I have a following code (see below) that Index sheets dynamically. Now is a problem to implement the code that color Index cells when I color that tab. For example when I color T1 tab in green it will color relevant cell in the INDEX tab. In this example it will be A2. When I color T3 tab code will color A3 cell in the INDEX tab and so on. See picture.
[VBA]Private Sub Worksheet_Activate()
Dim wSheet As Worksheet, l As Long
Application.ScreenUpdating = False
Me.Move Before:=Sheets(1)
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
.Cells(1, 1).Font.Bold = 1
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
' Uncomment for the first time then comment again
' .Rows("1:1").Insert Shift:=xlUp
' .Rows("1:1").Clear
.Range("A1").Name = "Start_" & .Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
.Range("A1").Font.ColorIndex = xlAutomatic
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start_" & .Index, TextToDisplay:=wSheet.Name
End With
End If
Me.Columns(1).AutoFit
Next wSheet
Application.ScreenUpdating = True
End Sub[/VBA]
[ATTACH=JSON]{"alt":"Click image for larger version Name:\tSample.JPG Views:\t1 Size:\t24.1 KB ID:\t1193524","data-align":"none","data-attachmentid":"1193524","data-size":"full","title":"Sample.JPG"}[/ATTACH]