Solved: Range object acting up in 2007

[ntrauger]

Anyone know why this would fail in Excel 2007?Range(Cells(53,6), Cells(53,106)).Interior.Color = 0 I get "Method range of object worksheet failed".

A much smaller column argument in the second Cells property will avoid the error, as will different syntax, but I'm curious what's going on here to throw an error. Doesn't look like it should.

[Kenneth Hobs]

Works fine for me in 2010 on Vista64. It will error 1004 if any of those cells are locked and the sheet is protected.

[p45cal]

Neither range nor cells is qualified so you're depending on defaults, which in turn depends on where the code is situated and/or what the activesheet is at the time.
For example, I wouls expect the code to fail if the code were in a userform's code module and a chart sheet was active at the time.
So, predictablty, the questions arise:
1.Where is the code?
2.What kind of sheet is active at the time?

[ntrauger]

Sounds like a good point. I have the code in a worksheet and nested in a with block something like:Set wsNew = Sheets.Add
With wsNew
.Range(Cells(53,6),Cells(53,106))
End Withwhere the cells properties are refering to the current sheet and the Range is refering to the new sheet. Doesn't make sense why a smaller argument would not throw the error, but perhaps I went too quickly and missed something. I'll have to check it again later this evening.

[snb]

Sheets.Add.cells(53,6).resize(,100).interior.colorindex=6

[Jan Karel Pieterse]

To correct your own code, replace


.Range(Cells(53,6),Cells(53,106))

with


.Range(.Cells(53,6),.Cells(53,106))

[p45cal]

picky picky:
Sheets.Add.cells(53,6).resize(,101).interior.colorindex=6

[ntrauger]

Alright. Thanks guys.