On 18/03/2012 1:32 AM, Colin Hayes wrote:

HI

I need to select rows which satisfy criteria contained in 2 different
columns.

For example , select those rows which have cells in column A with a
number below 200 and also have the word 'Green' in column F.

The cells should be selected rather than filtered if possible.


Grateful any help.

G'day Colin

this code will do what you want, it will also highlight those rows where
your criteria is met.

This will highlight the entire row (Light Green) for each row that matches.

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c
.EntireRow.Interior.Color = 5296274
End With
End If
Next c
End Sub

And this will only color those cell within a specified range, change the
( With c.Resize(1, 6) range to suit your needs e.g (1, 10).

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c.Resize(1, 6)
.Interior.Color = 5296274
End With
End If
Next c
End Sub

HTH
Mick.