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

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,376
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm trying to loop thru an array of sheets but get an error on the follwing line.

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

VBA Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
VBA Code:
With Sheets(MySheets(i)).Range(Sheets(MySheets(i)).Cells(2, 1), Sheets(MySheets(i)).Cells(LastRow, LastCol))
 
Upvote 1
Solution
Thank you Cubist. That's exactly what it was.
 
Upvote 0

Forum statistics

Threads
1,226,739
Messages
6,192,739
Members
453,755
Latest member
IQBS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top