How do I get google sheet conditional formatting custom formulas to refer to relative cells?

"A"&ROW()

Returns A8 if you're on row 8 (regardless of column), A9 if you're on row 9, &c.

You can use this along with INDIRECT(), which takes the value of a cell given by a string.

INDIRECT("A"&ROW())

Returns the value in A8 if you're on row 8, the value in A9 if you're on row 9, &c.

---

You can also use R1C1 notation to generalize this to columns as well:

INDIRECT( "R" & ROW() & "C" & COLUMN() )

$ is to "anchor" to that row/col. If you want the column to stay static, use $A2 and the row can adjust freely.

Phew, finally figured it out - I can change the absolute references to be row/column specific, the trouble was that $A$2 was referencing and exact cell and A2 was relative to both row and column - I wanted to use $A2 so that only row is relative, so the answer to my question is to use this formula:

=SEARCH("Order",$A2)