Add sheets to array and select

Justice98

New Member
Joined
Jun 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I got 5 sheets with different tabcolors. The tab of a sheet becomes red when a certain value on the sheet isn't met. When it is, it becomes green. The only sheets I want to select as an array are the green sheets. How can I do this?
For example:
Sheet 1 tab color = Green
Sheet 2 tab color = Red
Sheet 3 tab color = Green
Sheet 4 tab color = Red
Sheet 5 tab color = Green

In the example I just want to select sheets 1, 3 and 5. But the color can change based on the cell value. So how can I do this based on the cell value or tabcolor?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub justice()
   Dim Ws As Worksheet
   
   With CreateObject("scripting.dictionary")
      For Each Ws In Worksheets
         If Ws.Tab.Color = 5287936 Then .Item(Ws.Name) = Empty
      Next Ws
      Sheets(.keys).Select
   End With
End Sub
change the 5287936 to match the green you are using
 
Solution

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
133
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
Sub test2()
    Dim tbs$, i&
    For i = 1 To Sheets.Count
        With Sheets(i)
            'If .Tab.Color = vbGreen Then
            If .Tab.Color = ActiveCell.Interior.Color Then
                tbs = tbs & "," & .Name
            End If
        End With
    Next i
    If tbs <> "" Then Sheets(Split(Mid(tbs, 2), ",")).Select
End Sub
 

Justice98

New Member
Joined
Jun 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello Fluff and veyselemre,

Thank you both for the fast replies and your VBA Codes, they work perfect! I couldn't figure it out myself.

Kinds regards,

Justin
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,147,747
Messages
5,742,966
Members
423,769
Latest member
LongToast

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