Find Method for finding the next value in a range GT certain value

[MichaelDavid]

Greetings! Does anyone know of an efficient method or function for finding
the next value in a range which is greater than a specified value? My macro
would first select the range, look for the first/next value in a range
greater than a specified value. My macro would process that value, and
continue by looking for the next value in the range greater than the
specidied value. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Greetings! I had a few typos in this post. It should read:
"Does anyone know of an efficient method or function for finding
the first/next value in a range which is greater than a specified value? My
macro
would first select the range, look for the first/next value in that range
which is
greater than the specified value. My macro would process that value, and
continue by looking for the next value in the range greater than the
specified value. "
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Charabeuh]

Something like that ?

Option Explicit

Sub test()

Dim Xcell
Dim Limit ' the value to compare with
Dim MyRange As Range 'the range to search through

Limit = 2
Set MyRange = Range("H2:H17")

For Each Xcell In MyRange
If Xcell.Value > Limit Then
'do what you have to do with Xcell
'exemple :substract 2 to the value
'and modify the font
Xcell.Value = Xcell.Value - 2
Xcell.Font.Italic = True
Xcell.Font.Bold = True
Xcell.Font.Color = vbRed
End If
Next Xcell

set MyRange=Nothing

End Sub

 

[MichaelDavid]

Greetings Charabeuh!

Thanks for your suggestion. My actual code is:

Dim CellPrice As Range
Dim LstRowData As Long
Dim CurrentPrice As Double

With ActiveSheet
LstRowData = .Range("O2")
.Range("M8:M" & LstRowData).UnMerge
CurrentPrice = .Range("H4")

For Each CellPrice In .Range("M8:M" & LstRowData)
If CellPrice.Value > 8# * CurrentPrice Then
CellPrice.Interior.Color = vbYellow
End If
Next
End With

But I was hoping to find something more efficient. I am a speed fanatic. May
you have a blessed day.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick