CODEWITHSUNDEEP
excelvba
Svetlozar Lazarov
Svetlozar Lazarov

Reputation: 11

How to run VBA code in excel for each sheet

I need a help. with this code:

Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    For Each ws In Workbooks("Book1.xlsm").Worksheets
        If ws.Target.Address = "$A$1" Then
            Select Case Target.Value
            Case Is <> ""
                Me.Tab.Color = vbGreen
            Case ""
                Me.Tab.Color = vbWhite
            Case Else
                Me.Tab.Color = vbBlue
            End Select
        End If
    End Sub

It is not working, but I don't know why. Any ideas? Thanks in advance.

Upvotes: 0

Views: 68

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9568

In ThisWorkbook module put the code like that

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Target.Value
            Case Is <> ""
                Sh.Tab.Color = vbGreen
            Case ""
                Sh.Tab.Color = vbWhite
            Case Else
                'This will not happen as it is either empty or non-empty
                Sh.Tab.Color = vbBlue
        End Select
    End If
End Sub

Upvotes: 2

Related Questions