converting an if+or+indirect function to vba

Hello im trying to convert this function:

=IF(OR(INDIRECT("'sheet1'!C8")="Nouveau locataire",INDIRECT("'sheet1'!C8")="Décès"),CELL("contents",INDIRECT("'sheet1'!B8")),"")

to something similar to :

Sub if_orfuction()

Dim i As Integer j = 2

For i = 2 To Sheets("Sheet1").Range("A1").SpecialCells(xlLastCell).Row

    If Sheets("Sheet1").Cells(i, 2).Value = "Nouveau Locataire" Or Sheets("Sheet1").Cells(i, 2).Value = "Décès" Then

        Sheets("Sheet3").Cells(j, 1) = Sheets("Sheet1").Cells(i, 1)
        j = j + 1

    End If

Next i

End Sub

how can i implement the indirect in the fuction?