VBA set conditional formatting application-defined or object-defined error

[FryGirl]

I'm trying to loop thru an array of sheets but get an error on the follwing line.

With Sheets(MySheets(i)).Range(Cells(2, 1), Cells(LastRow, LastCol))

Sub SetCF()
    Dim LastRow As Long: LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim LastCol As Long: LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Const Fm1$ = "=OR($D2=""Major Task"",$D2=""Workcenter"")"
    Const Fm2$ = "=$A2=3"
    Const Fm3$ = "=E2>$L2"
    Const Fm4$ = "=E2<$M2"
    Const Fm5$ = "=$G2=""P"""
    Const Fm6$ = "=$G2=""O"""
    Dim MySheets As Variant: MySheets = Array("PATS", "FREQS", "MMHRS")
    Dim i As Long
    For i = LBound(MySheets) To UBound(MySheets)
        With Sheets(MySheets(i)).Range(Cells(2, 1), Cells(LastRow, LastCol))
            With .FormatConditions
                .Delete
                With .Add(Type:=xlExpression, Formula1:=Fm1)
                    .Borders.LineStyle = xlContinuous
                    .Interior.Color = RGB(217, 217, 217)
                    .Font.Bold = True
                    .StopIfTrue = False
                End With
                With .Add(Type:=xlExpression, Formula1:=Fm2)
                    .Borders.LineStyle = xlContinuous
                    .Interior.Color = RGB(217, 225, 242)
                    .StopIfTrue = False
                End With
            End With
        End With
        With Sheets(MySheets(i)).Range(Cells(2, 5), Cells(LastRow, LastCol - 4))
            With .FormatConditions
                With .Add(Type:=xlExpression, Formula1:=Fm3)
                    .Borders.LineStyle = xlContinuous
                    .Interior.Color = RGB(244, 176, 132)
                    .Font.Bold = True
                    .StopIfTrue = False
                End With
                With .Add(Type:=xlExpression, Formula1:=Fm4)
                    .Borders.LineStyle = xlContinuous
                    .Interior.Color = RGB(142, 169, 214)
                    .Font.Bold = True
                    .StopIfTrue = False
                End With
                .Item(3).Priority = 1
                .Item(4).Priority = 2
            End With
        End With
    Next i
End Sub

 

[Cubist]

It's likely due to referencing cells without explicitly specifying the worksheet. When you use Cells, it refers to the active worksheet, but within your loop, you're referring to cells on different sheets. Try

With Sheets(MySheets(i)).Range(Sheets(MySheets(i)).Cells(2, 1), Sheets(MySheets(i)).Cells(LastRow, LastCol))

 

[FryGirl]

Thank you Cubist. That's exactly what it was.

 

[Cubist]

You're welcome.