VBA help to colour TOC cell with corresponding tab colour

Marcus131975

New Member
Joined
Feb 9, 2018
Messages
14
Hi

The below extract from VBA code creates a Table of Contents (based on worksheet names) and additionally adds a comments column and a further column with the numeric value of the corresponding tab colour.

Is there a way to amend it so that the cell in column F is actually the same colour as the tab, rather than just populated with the numeric value of that colour?

'Loop through all sheets
For Each ws In wb.Worksheets
If ws.Name <> wsTOC.Name Then
If ws.Visible = True Then
With wsTOC
.Range("B" & r) = ws.Name
.Range("E" & r) = ws.Range("CA1")
.Range("F" & r) = ws.Tab.Color
End With
End If

If ws.Visible = True Then
wsTOC.Hyperlinks.Add Anchor:=wsTOC.Cells(r, 2), Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
End If
End If
r = r + 1
Next ws
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Replace that line with ...
VBA Code:
  .Range("F" & r).Interior.Color = ws.Tab.Color


Rich (BB code):
'Loop through all sheets
For Each ws In wb.Worksheets
    If ws.Name <> wsTOC.Name Then
        If ws.Visible = True Then
            With wsTOC
                .Range("B" & r) = ws.Name
                .Range("E" & r) = ws.Range("CA1")
               .Range("F" & r).Interior.Color = ws.Tab.Color
            End With
        End If
        If ws.Visible = True Then
            wsTOC.Hyperlinks.Add Anchor:=wsTOC.Cells(r, 2), Address:="", _
            SubAddress:="'" & ws.Name & "'!A1"
        End If
    End If
    r = r + 1
Next ws

In future remember to use code tags around your code
If you want to format the font to highlight something ... click on RICH icon ... otherwise click on VBA icon

[ CODE=rich]code goes here[/CODE]


[ CODE=vba]
code goes here[/CODE]
 
Last edited:

Marcus131975

New Member
Joined
Feb 9, 2018
Messages
14
Replace that line with ...
VBA Code:
  .Range("F" & r).Interior.Color = ws.Tab.Color


Rich (BB code):
'Loop through all sheets
For Each ws In wb.Worksheets
    If ws.Name <> wsTOC.Name Then
        If ws.Visible = True Then
            With wsTOC
                .Range("B" & r) = ws.Name
                .Range("E" & r) = ws.Range("CA1")
               .Range("F" & r).Interior.Color = ws.Tab.Color
            End With
        End If
        If ws.Visible = True Then
            wsTOC.Hyperlinks.Add Anchor:=wsTOC.Cells(r, 2), Address:="", _
            SubAddress:="'" & ws.Name & "'!A1"
        End If
    End If
    r = r + 1
Next ws

In future remember to use code tags around your code
If you want to format the code click on RICH icon (otherwise click on VBA)

[ CODE=rich]code goes here[/CODE]


[ CODE=vba]
code goes here[/CODE]



Thanks you very much and noted about the code tags. Apologies for that.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
You are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,129,714
Messages
5,637,940
Members
416,993
Latest member
ant8989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top