Excel 2013 Macro only running on 1 Sheet within multi sheet Excel Workbook

Hi There,

I would really appreciate some asssistance with this. This Macro is only running successfully on 1 Excel Sheet within an Excel work book that has around 10 sheets. I am wanting it to only run on the active sheet that I am working in at the time. I do not want it to run on all sheets at the same time.

Have I dont something wrong with the code or is there something further I can add to specifiy this.

Private Sub Workbook_Open()
Activesheet.Unprotect Password:="Yourpasswordhere"
Dim colorIndex As Integer
colorIndex = 3
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In ActiveSheet.UsedRange.Cells
Dim color As Long
color = xRg.Interior.colorIndex
If (color = colorIndex) Then
xRg.Locked = True
End If
Next xRg
Application.ScreenUpdating = True
Activesheet.Protect Password:="Yourpasswordhere"
End Sub

i think your using the wrong event

try it under the workbook_sheetactivate event

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim colorIndex As Integer
    Dim xRg As Range
    Dim color As Long
    
    Sh.Unprotect Password:="Yourpasswordhere"
    
    colorIndex = 3
    
    Application.ScreenUpdating = False
    For Each xRg In Sh.UsedRange.Cells
    
        color = xRg.Interior.colorIndex
        If (color = colorIndex) Then
            xRg.Locked = True
        End If
    Next xRg
    
    Application.ScreenUpdating = True
    Sh.Protect Password:="Yourpasswordhere"

End Sub

thank you...please check post #2

Whichever sheet was the active sheet when last saved will be the ActiveSheet when opening your workbook again.
If you were working on sheet5, saved the workbook and closed it, when opening the workbook it will open with sheet5 as active sheet.
It does not sound like you want the code to run when opening the workbook but when you are changing cells.

Or

Do you want to run the code just once with a button?

Post #2 was updated. Is that what you have in mind?

Or this?

Sub Lock_Red_Cells()

Dim lc As Long, lr As Long, c As Range

lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row

For Each c In ActiveSheet.Range(Cells(1, 1), Cells(lr, lc))

    If c.Interior.Color = vbRed Then c.Locked = True

Next c

End Sub

I have just tested code in post #2 and it is working perfectly. Thanks so much. I have marked thread as solved. Thanks again guys.