As far as I understand, your are using the intersection operator (space) in your INDEX formula: "$C2531:$C2731 <1". This will compare every cell between in one range ($C2531$C2731) against another range, but in this case a "<1" comparison instead. So the Intersection will return a range containing a TRUE or FALSE value for each comparison (ie. an array formula). MATCH will then look at that resulting range for "TRUE" values.
In other words, the formula is relatively complex behind the scenes, and although it is short to read, in VBA you want to keep things as simple and clear as possible from a "behind the scenes" perspective - or you'll run into problems. What you are trying to achieve is simple, yet cramming INDEX and MATCH functions into VBA is not.
In Visual Basic, there's no native intersection operator. From what I can find, the only equivalent to the is the Application.Intersect function, which takes Range objects as parameters. Unfortunately, although you can create the first range as "Set myRange = Range("$C2531:$C2731"), I have not been able to create a Range object of "<1" in VBA, as this is not a valid cell range. So it seems the Intersection Operator in Excel Formulas is more capable/flexible than the Application.Intersect method in VBA. And that's the point of failure here ("$C2531:$C2731 <1" is not valid VBA)
Now you could spend a day trying to see if you can manage to intersect a range and a "<1" in VBA, but there are simpler ways to do this in VBA. If it was me, I would not use Worksheet functions, and just use a standard For...Next loop to go over your rows and do the comparison in plain visual basic for each cell in your Range. If one comparison match, return the row number.
To get an idea, it would look something like:
Dim myRange As Range
Set myRange = Range("$C2531:$C2731")
For y = 1 to myRange.Cells.Rows.Count
If y < 0 Then
result = myRange.Cells(y,1).Row