Resize a Range error

Hi all

Can anyone explain this code snippet and why if cell y11 is zero it gives an error ?

Range("b11").Resize(, Range("y11").Value * 2).Interior.ColorIndex = 4

Derek McGill

It looks to me that it sets the background colour of a block of cells to be lime green.  The number of cells it sets to green depends on the value in y11.  The range of cells to be coloured green starts with b11 on the left and extends to the right y11*2 cells.  

The reason you get an error when y11 is zero (or probably blank too) is because it is attempting to select zero cells.  If this is a problem, my suggestion would be to add a quick check before the operation:

If range("y11").value > 0 then Range("b11").Resize(, Range("y11").Value * 2).Interior.ColorIndex = 4

So, if y11 is 0 (or blank or negative), no cell colours will be turned green.

"Resize" specifies the size of a range... Resize(number of rows, number of columns)
A range cannot have zero columns.

You might try...
If Range("y11").Value > 0 Then _
   Range("b11").Resize(, Range("y11").Value * 2).Interior.ColorIndex = 4
(of course, that requires that cell y11 never have any text in it)

Thanks Rick

The worksheet is for a chess score table which shows players max/actual scores,

and at the start they all have zero, Simple error trapping is not something I do well. thanks for your code and time

Derek McGill

Thanks for the info. there will not be text in the cell, code works well.

Derek McGill