Referencing an inactive worksheet's cell in an active worksheet's cell during an if statement

[Ruky]

Hi,

I want to refer to a cell as part of my If..End if condition. I know how to do this if this cell is in the same worksheet. But how do you refer to another worksheet's cell?

Let's say I am working on worksheet 1's cell(1,1) and I want to refer to worksheet 2's cell (1,2)

Sample if statement:

if cell(1,1)=2 and worksheet 2's cell (1,2)=3 then

...

end if

Thanks,

 

[Trevor G]

Do you want this as formula or VBA code?

 

[VoG]

Try like this

If Cells(1, 1).Value = 2 And Worksheets("test.xls").Cells(1, 2).Value = 3 Then

test.xls must be open.

 

[Ruky]

Hi,

I want to run this in a loop in VBA. The worksheets are in the same workbook.

Thanks,

 

[VoG]

Sorry, my original reply was rubbish. You would need something like

If Cells(1, 1).Value = 2 And Worksheets(i).Cells(1, 2).Value = 3 Then

 

[Ruky]

Alright, I adapted it into the code I am working on but now I have an application/object defined error (Run time error 1004). Here's the piece of my code:

For Each mycell In Range("u5:u20")
mycell.Select
text = mycell.Offset(0, -18)
If mycell.Offset(0, -19) <> "" And mycell.EntireRow.Font.ColorIndex = 3 Then
If Workbooks(1).Sheets("Refer").Range(Cells(n, 1)) = text Then

...
End If

If Workbooks(1).Sheets("Refer").Range(Cells(n, 1)) <> text Then

...
End If
End If

Next mycell