Setting a cell's value and background colour through VBA

PMHunt1955 

Matter is now resolved through use of a Worksheet_Activate sub-routine.

Code =

Private Sub Worksheet_Activate()

Dim wb As Workbook
Dim ws() As Worksheet
Dim rng As Range
Dim rngNow As Range
Dim wsCount As Integer
Dim wsColor() As String
Dim wsPrime As Worksheet

Dim I As Long

Set wb = ThisWorkbook
Set wsPrime = wb.Worksheets("Coverage_Statistics") 'Targets Coverage_Statistics for placement of Table of Contents

wsCount = wb.Names("Main_Families_Count").RefersToRange.Value

Set rng = wsPrime.Range("A1:B" & wsCount)
ReDim ws(1 To wsCount)
ReDim wsColor(1 To wsCount)

For I = 1 To wsCount

Set ws(I) = Worksheets(I)
wsColor(I) = ws(I).Tab.Color
Set rngNow = rng(I + 1, 1)
rngNow.Interior.Color = wsColor(I)
rngNow = ws(I).Name

Next I

End Sub