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

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,368
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

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