Return a range object using an IF statement in VBA

I'm a beginner with VBA and am looking for help with my question. I am trying to do the following:

1. Loop through a column in my spreadsheet to find the row corresponding to two a combination of two values
2. Then loop through the row corresponding to that combination going horizontally to return the range of all values that pass an if-statement (>40) (the set of numbers that pass the if statement are always sequential & only one set per row i.e. (15, 34, 32, 42, 45, 56, 67, 56, 43, 39, 23, 14)
3. I would like to be able to also return the column number of the first and last value that pass the if statement to be able to extract a date range in another row

In pseudo code it would be something like this:

For r = 1 To 10000 'Loop through 10000 rows to find the correct ACV field
                If WkSht.Range("B" & r).Value = "%ACV" And WkSht.Range("C" & r) = bp_upc Then
                    'For column in row(r)
                        'If column > 40 add cell address to range object to be returned
                    'Next Column
                'End If

Then use the column component of the first and last cell address in that range to get the values in another row.

Any help or tips would be much appreciated.