CODEWITHSUNDEEP
excelvba
Brianna Cates
Brianna Cates

Reputation: 347

Selecting cells when criteria is met

I have a worksheet that I am trying to get it to look through column B to see if it is highlighted and then select that column and its associate column A value. I have written something and it selects the last cell that meets the criteria but not all of them. Anybody know what is happening? I have provided my code below. Thanks in advance

ws.Select
For Each rng In ws.Range("C2:C" & lastrow)
If rng.Interior.Color = 65535 Then
ws.Range("B" & rng.Row).resize(1,2).select
End If
Next rng

Upvotes: 1

Views: 2207

Answers (2)

Fadi
Fadi

Reputation: 3322

Try this code:

Dim uRng As Range  
ws.Select
For Each Rng In Ws.Range("C2:C" & LastRow)
 If Rng.Interior.Color = 65535 Then
    If uRng Is Nothing Then
     Set uRng = Ws.Range("B" & Rng.Row)
    Else
     Set uRng = Union(uRng, Ws.Range("B" & Rng.Row))
    End If
 End If
Next Rng

If Not uRng Is Nothing Then uRng.Select

When we select a cell then select another cell, the first selection will removed so we need to select all cells in one Select action, by using Union we can do that

Upvotes: 1

user5889203
user5889203

Reputation:

Something like this should work, but you should test it on a copy, not your original.

dim myRange as Range

ws.Select
For Each rng In ws.Range("C2:C" & lastrow)
If rng.Interior.Color = 65535 Then
set myrange = myrange union (ws.Range("B" & rng.Row))
End If
Next rng

myRange .resize(1,2).select

Upvotes: 0

Related Questions