CODEWITHSUNDEEP
vbaexcel
cjgc
cjgc

Reputation: 17

Code not cycling through sheets

My Workbook has several sheets named Detail1, Detail2, Detail3, and so on. I´d like to color the sheet according to the content of cell B15. However the code is only coloring sheet Detail1 and not cycling through the other sheets. What am I doing wrong?

    Sub Color_by_cell_value()
Dim X As String
Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name Like "Detail*" Then
            X = Range("B15").Value
            Select Case X
            Case Is = "no"
                Range("A1:AZ100").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorAccent3
                    .TintAndShade = 0.799981688894314
                End With
                Range("B15").Select
                With Selection.Interior
                    .Color = 5296274
                End With
            Case Is = "yes"
                Range("A1:AZ100").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.599993896298105
                End With
                Range("B15").Select
                With Selection.Interior
                    .Color = 255
                End With
            Case Is = "yes/no"
                Range("A1:AZ100").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorAccent6
                    .TintAndShade = 0.799981688894314
                End With
                Range("B15").Select
                With Selection.Interior
                    .Color = 65535
                End With
            End Select
        End If
    Next ws
End Sub

Upvotes: 1

Views: 69

Answers (2)

agold
agold

Reputation: 6286

You can also use ws.Activate:

Sub Color_by_cell_value()
Dim X As String
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets

    If ws.Name Like "Detail*" Then

        ws.Activate
        X = Range("B15").Value

        Select Case X
        Case Is = "no"
            Range("A1:AZ100").Select
            With Selection.Interior
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = 0.799981688894314
            End With
            Range("B15").Select
            With Selection.Interior
                .Color = 5296274
            End With
        Case Is = "yes"
            Range("A1:AZ100").Select
            With Selection.Interior
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = 0.599993896298105
            End With
            Range("B15").Select
            With Selection.Interior
                .Color = 255
            End With
        Case Is = "yes/no"
            Range("A1:AZ100").Select
            With Selection.Interior
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0.799981688894314
            End With
            Range("B15").Select
            With Selection.Interior
                .Color = 65535
            End With
        End Select
    End If
Next ws

Application.ScreenUpdating = True

End Sub

Note I also set the Application.ScreenUpdating to False as a good custom.

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6982

There is lots of ways to edit this code and make it more efficient, but to answer your question. ws.select would be required after line.If ws.Name Like "Detail*" Then

  For Each ws In Worksheets
    If ws.Name Like "Detail*" Then
        ws.Select
        X = Range("B15").Value
        Select Case X

Upvotes: 1

Related Questions