Cell Ranges within sheets

[Flue]

HI all.

Can some explain why this code;

Range(Cells(FoundCell.Row, 1), Cells(FoundCell.Row, 6)).Interior.ColorIndex = 6

works for the main sheet, but as soon as you want to assign a sheet name (if working with multiple sheets) to it you have to use;

Sheets("TEST1_15").Range("A" & i & ":F" & i).Interior.ColorIndex = 6

Even this works;

 Sheets("TEST1_15").Range(Cells(FoundCell.Row, 1)).Interior.ColorIndex = 6

But add that second Cell for the range and it fails.

Any Idea?

Thanks.

Brian

 

[VoG]

This will work

Sheets("TEST1_15").Range(Sheets("TEST1_15").Cells(i, 1), Sheets("TEST1_15").Cells(i, 5)).Interior.ColorIndex = 6

 

[Flue]

Kind of making a little sense.

If using this:

Sheets("TEST1_15").Range(Cells(i, 1), Cells(i, 5)).Interior.ColorIndex = 6

from a module it works just fine...once the code is plugged in the sheet("LIVE1_15") VB editor, it fails.

I knew that was an issue, but it's been a while so I tend to forget...Most of my code(s) reside in modules and not on the sheets. I guess that kind of makes sense.

 

[VoG]

You need to fully qualify the range as here http://www.mrexcel.com/forum/showpost.php?p=2866343&postcount=11

 

[Flue]

VOG....that was the one combination I never tried. Thanks.