vba select more fill color tab

[kelvin_9]

Hi All,
I need some guidance on below code that how can I run this perfectly exclude sheet1(custom name: personalization)

Thank you very much

Sub No Fill()
Dim ws As Worksheet
Dim strg() As String
Dim count As Integer
count = 1
 
    For Each ws In Worksheets
        If ws.Tab.Color = 0 Then
            ReDim Preserve strg(count) As String
            strg(count) = ws.Name
            count = count + 1
        Else
        End If
    Next ws
    
    Sheets(strg(1)).Select
    For i = 2 To UBound(strg)
        Sheets(strg(i)).Select False
    Next i
 
End Sub

 

[Micron]

maybe

For Each ws In Worksheets
   If ws.Tab.Color = 0 And Not ws.Name = "personalization" Then
      ReDim Preserve strg(count) As String
      strg(count) = ws.Name
      count = count + 1
   End If
Next ws

I'm not seeing the need for the Else that you have there. Nor does that look like a valid procedure name, so is that "air" code?
Also, that only seems to build an array of sheets then does nothing with it as far as fill is concerned.

 

[Fluff]

You can also get rid of the 2nd loop like

Sub NoFill()
Dim ws As Worksheet
Dim strg() As String
Dim count As Integer

ReDim strg(1 To Worksheets.count)
    For Each ws In Worksheets
        If ws.Tab.Color = 0 And Not ws.Name = "personalization" Then
            count = count + 1
            strg(count) = ws.Name
        Else
        End If
    Next ws
    ReDim Preserve strg(1 To count)
    Sheets(strg).Select
End Sub

 

[kelvin_9]

Micron & Fluff
thank you very much for your quick reply

it works prefect and 2 more questions would like to ask
first, do you think i use sheet1 instead of the sheet name(personalization) avoid any change as precaution?
second, i have another code from someone here, i would like to run as same as this, run at all sheet exclude sheet1, can i post here?

sorry all for, the subject name should "vba select no fill color tab"

 

[Fluff]

do you think i use sheet1 instead of the sheet name(personalization) avoid any change as precaution?

Yes.

i have another code from someone here, i would like to run as same as this, run at all sheet exclude sheet1, can i post here?

Best start a new thread.

 

[kelvin_9]

Yes.

Best start a new thread.

what should i need to amend then?

 

[Micron]

i would like to run as same as this, run at all sheet exclude sheet1, can i post here?

Don't you already have that solution if you modify to use the sheet index number instead of the sheet name?

 

[Fluff]

what should i need to amend then?

Just start a new thread including the other code & explain what you are trying to do.

 

[kelvin_9]

sorry for any confusing to both Micron and Fluff

i love this code and this is just prefect
but i was thinking to use sheet1 instead of sheet name to avoid any change from others

thank you very much

 

[Fluff]

Opps. You would use

If ws.Tab.Color = 0 And Not ws.CodeName = "Sheet1" Then