Colin Hayes laid this down on his screen :
Colin
I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value & [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.
I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.
HTH
Mick.
Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c < vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub
Hi Mick
OK Thanks for that.
The problem I have is that I want to run an existing macro on the results
produced by this procedure. This depends on cells being selected rather than
filtered. If I were starting from scratch I would adopt the method that Don
has explained so well. Unfortunately though I'm having to fit this into an
exiting framework which wouldn't work on filtered results , and would be a
nightmare to unpick.
Thanks anyway for your time.
Best Wishes
The results return a 'Selection' in the form of an entire row. In this
case you could pass a ref to the selected row to the macro you want to
run on it. If there's multiple rows that qualify then you could built a
delimited string of their respective row nums and pass that to your
macro. So for example, modifying Mick's code sample, you could do
something like...
Sub MyMacro
Dim sRefs As String, rng As Range
Dim vCell As Range, cCell As Range
Set vCell = [K1]: Set cCell = [M1]
For Each rng in Range("A1:A10")
If rng < vCell And rng.Offset(, 5).Value = cCell Then _
sRefs = sRefs & "," & CStr(rng.Row)
Next 'rng
Call MyOtherMacro(Mid$(sRefs, 2)) 'trim leading comma beforehand
'Cleanup
Set vCell = Nothing: Set cCell = Nothing
End Sub
Sub MyOtherMacro(ByVal RowRefs As String)
Dim v As Variant
For Each v In Split(RowRefs, ",")
With Rows(v)
'do stuff
End With 'Rows(v)
Next 'v
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion