Select Row determined by Variable

[pergo]

Good Morning,

Basically I'm trying to come up with a script that selects a row based on a cell value.

I have an If clause that finds each cell in a range that is colored purple and returns it's value to another cell on a separate sheet.

What i'm trying to do now is to select the 1st cell of each row and return it's value to another cell on a separate sheet.

ActiveCell isn't helping with this because since this macro is on a separate sheet, it won't look up the correct rows.

Can anyone help with this? Much appreciated!!!

Below is my code, the bolded part is the problematic portion:

For Each Cell In SrcRng1
If Cell.Interior.Color = RGB(112, 48, 160) Then
Job = Cell
Rows(Cell.row).Select
Selection.Cells(1, 1).Select

NextJob.Select
ActiveCell = Job
NextTech.Select
ActiveCell = Tech

Set NextJob = NextJob.Offset(1, 0)
Set NextTech = NextTech.Offset(1, 0)
End If
Next Cell

 

[Damon Ostrander]

Hi Pergo,

If I correctly understand what you are trying to do, the problem is that in

Rows(Cell.row).Select

the Rows range is an unqualified reference, so Excel will assume it lies in the sheet containing the code. I'm assuming that Cell is a variable assigned to a range in another sheet. You could get your intended range reference right by doing this:

Cell.Parent.Rows(Cell.row).Select

which works because the parent of Cell is the worksheet containing Cell.

That being said, you could avoid the unqualified reference issue altogether by replacing the two bolded lines by:

Cell.EntireRow.Cells(1).Select

which simply selects the first cell in the row containing Cell, on whatever sheet it resides.

Keep Excelling.

Damon