If you have "Option Explicit" at the top of every code page AND you use the VBA Menu: Debug >> Compile, it will help you find many errors.

If you place the cursor inside a sub and press F8, VBA will step through the code by one line of code each time you press F8. This will tell you when an error occurs.

If you place the cursor inside a VBA Keyword and press F1, the help page for that Keyword will show.

I think I put a Parenthesis in the wrong place.

Try this
Sub Comparation()
  Dim TR As Range, Cel As Range
  
  For Each Cel In Range(Sheets("OPAR").Range("A" & Rows.Count).End(xlUp).Row)
    Set TR = Sheets("CONS").Range("B2:" & Range("B" & Rows.Count).End(xlUp).Address).Find(Cel)
    If Not TR Is Nothing Then Cel.Offset(0, 24) = TR.Offset(0, 2)
  Next Cel
End Sub