Reference another cell value within if/then statement

[rjh3m8]

Hi all,
I am trying to write a code that looks at a specific range, finds the maximum value in the range, then pops up a messagebox containing the value two cells ABOVE the cell with that max value.

So far, I have the max value calculating in a single cell on the sheet (in cell V12) and have a variable that looks to match it. I am struggling with the "if it matches, look up two cells and report that cell's value" part. I tried using "ActiveCell.ValueR1C1 = "=R[-2]C[0]", but that didn't work out.
Any ideas?

'Determine Freq Bandwidth
Dim rng As Range
Dim cell As Range
Dim loc
Dim num

Set rng = Range("C12:S12")

For Each cell In rng
On Error GoTo err_chk
loc = cell.Value
On Error GoTo 0

If loc = Range("V12").Value Then 'Range V12 is MaxValue of rng
num = ????
'ActiveCell.ValueR1C1 = "=R[-2]C[0]"
MsgBox num & " found in cell " & cell.Address(0, 0)
End If
Next cell



Additionally, I'm looking at the same range to find what cells in the range are greater than a given value. I was able to make a conditional formatting rule on the sheet itself, but need to know the value of the cell two above the first cell that meets the criteria and the last cell that meets the criteria in the specific range. Any help on this would be nice too.
err_chk:
loc = 0
Err.Clear
Resume Next

 

[offthelip]

try this

Sub test()
'Determine Freq Bandwidth
Dim rng As Range
Dim cell As Range
Dim loc
Dim num
Dim rowno As Integer
Dim colno As Integer
Set rng = Range("C12:S12")

For Each cell In rng
loc = cell.Value

If loc = Range("V12").Value Then 'Range V12 is MaxValue of rng
rowno = cell.Row
colno = cell.Column
num = Range(Cells(rowno - 2, colno), Cells(rowno - 2, colno))
'ActiveCell.ValueR1C1 = "=R[-2]C[0]"
MsgBox num & " found in cell " & Range(Cells(rowno - 2, colno), Cells(rowno - 2, colno)).Address
Exit For
End If
Next cell
End Sub

 

[rjh3m8]

try this

Sub test()
'Determine Freq Bandwidth
Dim rng As Range
Dim cell As Range
Dim loc
Dim num
Dim rowno As Integer
Dim colno As Integer
Set rng = Range("C12:S12")

For Each cell In rng
loc = cell.Value

If loc = Range("V12").Value Then 'Range V12 is MaxValue of rng
rowno = cell.Row
colno = cell.Column
num = Range(Cells(rowno - 2, colno), Cells(rowno - 2, colno))
'ActiveCell.ValueR1C1 = "=R[-2]C[0]"
MsgBox num & " found in cell " & Range(Cells(rowno - 2, colno), Cells(rowno - 2, colno)).Address
Exit For
End If
Next cell
End Sub

This worked great. Thanks so much!!