What is wrong with this VBA syntax in Excel please

[psbrown]

Hi

I am doing my first excel project and found how to define a range definition from the vba help.


my syntax is:
Worksheets("Chart").Range(Cells(2, 2), Cells(2,
NoCols)).Interior.ColorIndex = 6

Even removing the variable and using a constatnt as below
Worksheets("Chart").Range(Cells(2, 2), Cells(2,
6)).Interior.ColorIndex = 6


It still fails with
run time error 1004
application defined or object defined error


if I change the statement to
.Range("A1").Interior.ColorIndex = 6
it works ok.

Any help appreciated.


Paul

 

[GlennUK]

To change the interior of cells the sheet on which they reside must be activated. You last statement is defaulting to the active sheet.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PHV]

With Worksheets("Chart")
.Range(.Cells(2, 2), .Cells(2, 6)).Interior.ColorIndex = 6
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[psbrown]

Thanks PVH that did the trick,

Can you please explain why my statement was not working?

Paul

 

[PHV]

Because Cells(2, x) is a range of the active sheet, not necessarly of Worksheets("Chart").
Tip: always qualify a range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886