Loop sheets that has color

[dluhut]

Hello,

For some reason, it is not looping the sheets.

As you can see below, what I want is

• Loop through all sheets
• if sheet color is 'green' which is indicated by color index 5296274 then check
• if sheet name is not equal to "Consol" then protect the worksheet
• else, input formulas on I2 to I4 and then protect the sheet.

For some reason, when I try to check it, it's only looping 1 sheet, which through the msgbox, returns "Sheet1 Color: False"

Any idea what did I do wrong?

For Each sht In ThisWorkbook.Sheets
    shtColor = sht.Tab.Color
    shtName = sht.Name
    MsgBox shtName & " Color: " & shtColor
    If sht.Tab.Color = 5296274 Then
        Sheets(sht).Activate
        If sht.Name <> "Consol" Then
            With sht
                .EnableSelection = xlNoSelection
                .Protect = "abc"
            End With
        Else
            With sht
                .Range("I2").Select
                .Range("I2").Formula = "='" & sht.Name & " input tab'!I6"
                .Range("I3").Select
                .Range("I3").Formula = "='" & sht.Name & " input tab'!I7"
                .Range("I4").Select
                .Range("I4").Formula = "='" & sht.Name & " input tab'!I8"
                .EnableSelection = xlNoSelection
                .Protect = "abc"
            End With
        End If
    End If
Next sht

[Joakim N]

I tried to define shtColor as Long. Otherwise a Boolean value seems to be stored. I don't know why.

Sub Test()
    Dim shtColor As Long
    Dim sht As Worksheet
    
    For Each sht In ThisWorkbook.Worksheets
        shtColor = sht.Tab.Color
        shtName = sht.Name
        MsgBox shtName & " Color: " & shtColor
        If sht.Tab.Color = 5296274 Then
            sht.Activate
            If sht.Name <> "Consol" Then
                With sht
                    .EnableSelection = xlNoSelection
                    .Protect Password:="abc"
                End With
            Else
                With sht
                    .Range("I2").Select
                    .Range("I2").Formula = "='" & sht.Name & " input tab'!I6"
                    .Range("I3").Select
                    .Range("I3").Formula = "='" & sht.Name & " input tab'!I7"
                    .Range("I4").Select
                    .Range("I4").Formula = "='" & sht.Name & " input tab'!I8"
                    .EnableSelection = xlNoSelection
                    .Protect Password:="abc"
                End With
            End If
        End If
    Next sht
End Sub

[dluhut]

Thanks for your help Joakim!

I was playing around and found the solution.

I just had to change

ThisWorkbook.Sheets

to

WorkSheets