Object variable or With block variable not set error

I am relatively new to VBA and came across this error for a segment of code I have been working on - it worked when I recorded the macro but not when I run it, and I know that there are definitely cells available of the format that I am looking for, but I just wondered if anyone could point me in the right direction?

I am trying to find the first occurrence of a cell with a particular colour background, regardless of the text within the cell, and what I have so far (from recording a macro, then updating it slightly) is the following:

Sheets("KPI (2)").Visible = True
Sheets("KPI (2)").Select
Range("A1").Select

With Application.FindFormat.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
.PatternTintAndShade = 0
End With

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate

Thanks in advance!

Dim rng As Range

set rng=Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate 

If Not rng Is Nothing Then

' the rest of the code

end if

I have just tried this, but I still get the same error message.

Which line of the code errors (Highlighted) ?
Probably this line

With Application.FindFormat.Interior

It's the Cells.Find part of it that errors

I do not know why the code errors on the line.
What is "What:="" ?

Why are you to find a blank cell any way?
If this does not work, please attach a sample with the code.


Set rng = cells.Find(" ", LookIn:=xlValues, lookat:=xlWhole)

The "What:="" part is what the macro recording gave me when I searched based on no text criteria

I think my problem may be that I don't want to find a blank cell, I want to find the cell based on the background colour, regardless of what text is in the cell, but I assumed that as that was how the find worked when recording the macro, it would do the same thing when I played it back.
In which case, do you know of a way of doing a search by format of cell, regardless of the text within the cell?

In that case, should be

Set rng = cells.Find("*", searchformat:=True)

I'm afraid I'm still getting errors on that line!
The following it a screenshot of a section of the sheet I'm working on, in case it helps identify where I am going wrong! (edited to remove anything confidential)
Excel sheet.jpg
I am basically trying to navigate to the start of the red cells to be able to update them (as they are missing from the above data in blue, which comes from PowerPivot) where the length of the blue and red sections are variable.

Code now reads (up to point of error) -

Sheets("KPI (2)").Visible = True
Sheets("KPI (2)").Select
Range("A1").Select

With Application.FindFormat.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
.PatternTintAndShade = 0
End With

Dim rng As Range

Set rng = Cells.Find("*", SearchFormat:=True).Activate

Please attach an excel sample book, not a picture. I can not test your code on a pic.

Sorry about that, apparently not all with it today - see attached!

The code does not error. Yes, it is not returning any values as the interior color is wrong.

Sub test1()
Sheets("KPI (2)").Visible = True
Sheets("KPI (2)").Select

  With Application.FindFormat
        .Clear
        .Interior.Color = 12040422
    End With

Dim rng As Range

Set rng = Cells.Find("*", SearchFormat:=True)

 If Not rng Is Nothing Then
   MsgBox rng.Address
 
 End If
End Sub

Very strange, as I definitely recorded the macro using the colours in the sheet I sent!
But regardless, thank you so much for your time in helping! It would have taken me considerably longer to spot that, so very much appreciated