Drawing a border around Cell Ranges

**Excel_queries** · 02-26-2009, 11:34 AM

Hi

I want to draw a border around a Range of Cells in excel sheet using VBA. I want the border which comes when we select a chart in excel. i.e. a blue colored border with which we can use any four corners to move that border by one cell.

I need to do this using VBA.

**Andy Pope** · 02-26-2009, 11:44 AM

That blue border is the one in xl2007?

That type of bordering is for objects rather than cells. Whilst you maybe able to use a shape to display the border you will not be able to code against it.

If you assign a macro it will run before you can resize the shape.
If you right click the shape to resize it will not run the macro.

**Excel_queries** · 02-27-2009, 04:46 AM

Is it possible to create a custom object which contains a cell reference? That border is the one that is associated with charts or the one that appears when we give a cell reference in formulas.

The border is a blue colored one which is expandable in all directions. In case of charts there are multiple such borders, blue one is the one that indicates the actual data set of the chart. You can see this by creating a chart and just selecting it.

**Andy Pope** · 02-27-2009, 07:02 AM

I know the border you mean I was just confirming as your profile says xl2003 and you made no mention of which particular version you are looking at.

As I said, there is no actual border object.
You could mimic it by using an empty chart object and a class module to capture the resize event.

In the attached you need to press the Initialize button, this will allow you to then select the chart. It's there with it's border in the unselected state over the red cells.
If you resize the chart with the ALT key pressed, in order to make the chart snap to row/column boundaries. It will colour the cells within the border red.

Without the ALT key the colour may have a gap either just inside or outside the border.

Not sure how much use this is though.