Format set range to specific currency format all worksheets

Question

not sure why this isnt working, but the idea is to open all worksheets, unprotect, format cells to currency, protect again whilst enabling filters. this works for protect and unprotect but trying to format I get an error message:

Unable to set the numberformat property of the range class

Sub UnProtect()

    Dim cell_to_format As Range

      ' Loop through all sheets in the workbook

      For i = 1 To Sheets.Count

         Sheets(i).UnProtect "T1T3NPM"

         With ActiveSheet

       Range("L8:L99999").Cells.NumberFormat = "£#,##0.00"

        Range("O8:O99999").NumberFormat = "£#,##0.00"

        Range("R8:R99999").NumberFormat = "£#,##0.00"

        Range("U8:U99999").NumberFormat = "£#,##0.00"

        Range("X8:X99999").NumberFormat = "£#,##0.00"

        Range("AA8:AA99999").NumberFormat = "£#,##0.00"

        Range("AD8:AD99999").NumberFormat = "£#,##0.00"

    .Protect Password:="T1T3NPM", AllowFiltering:=True
    .EnableSelection = xlUnlockedCells
End With

      Next i

End Sub

thanks in advance

Answer 1

With ActiveSheet should be With Sheets(i).

You are unprotecting Sheets(i) but working with the Activesheet which may or may not be Sheets(i)

Also you need to use Worksheets rather than Sheets else you will get an error if there are chart sheets.

So change Sheets(i) to Worksheets(i)

You will also need to fully qualify the range object else it will work with the Activesheet and not with Worksheets(i)

With Worksheets(i)
    '~~> Notice the DOT before Range
    .Range("L8:L99999").Cells.NumberFormat = "£#,##0.00"
End With