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 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
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
Unionto select all of them.unionis what you would use if you want to leverage your existing code