Is it possible to apply a border around groups of data.

For example, give the the sample sheet below, a border would surround the
groups of fruit. Borders would highlight the Apple, Orange, and Pear
sections.

A B C
====================
Apple 12 13
Apple 13 2
Apple 4 5
Orange 40 12
Pear 45 13
Pear 4 11

Is thier a simple way to have this automatically applied or must it be done
programmatically. The data within the sheet is refreshed daily. In lieue of
setting the borders manually, I'd like to have a more automated approach.

If it must be done programmatically, what would the routine look like in
general. Would I need to scan the column containing the grouping field to
help define the range or is their another approach to setting the borders?
Lastly, can the code be tied to an event that run when the workbook is opened
and the borders haven't been applied?

modify to suit

Sub bordersaround()
Worksheets("Sheet1").Range("A14").BorderAround _
ColorIndex:=3, Weight:=xlThick
'Selection.bordersaround
End Sub


Sub noborders()
Worksheets("Sheet1").Range("A14") _
.Borders.LineStyle = xlNone
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Orlanzo" <(E-Mail Removed)> wrote in message
news:314419E4-6DC4-484E-805D-(E-Mail Removed)...
> Is it possible to apply a border around groups of data.
>
> For example, give the the sample sheet below, a border would surround the
> groups of fruit. Borders would highlight the Apple, Orange, and Pear
> sections.
>
> A B C
> ====================
> Apple 12 13
> Apple 13 2
> Apple 4 5
> Orange 40 12
> Pear 45 13
> Pear 4 11
>
> Is thier a simple way to have this automatically applied or must it be
> done
> programmatically. The data within the sheet is refreshed daily. In lieue
> of
> setting the borders manually, I'd like to have a more automated approach.
>
> If it must be done programmatically, what would the routine look like in
> general. Would I need to scan the column containing the grouping field to
> help define the range or is their another approach to setting the borders?
> Lastly, can the code be tied to an event that run when the workbook is
> opened
> and the borders haven't been applied?
>

I would be inclined to use conditional formatting based on whether the vlaue
in column A changes. Format -> Conditional Format -> Formula =$A2<>$A1
(applied to the entire column)...
--
HTH...

Jim Thomlinson


"Orlanzo" wrote:

> Is it possible to apply a border around groups of data.
>
> For example, give the the sample sheet below, a border would surround the
> groups of fruit. Borders would highlight the Apple, Orange, and Pear
> sections.
>
> A B C
> ====================
> Apple 12 13
> Apple 13 2
> Apple 4 5
> Orange 40 12
> Pear 45 13
> Pear 4 11
>
> Is thier a simple way to have this automatically applied or must it be done
> programmatically. The data within the sheet is refreshed daily. In lieue of
> setting the borders manually, I'd like to have a more automated approach.
>
> If it must be done programmatically, what would the routine look like in
> general. Would I need to scan the column containing the grouping field to
> help define the range or is their another approach to setting the borders?
> Lastly, can the code be tied to an event that run when the workbook is opened
> and the borders haven't been applied?
>

Jim,
I applied the formula, not to the entire column, but to the first column of
the rows comprising the range. It had no affect. The range begins at A3, so
I modified the formula to erad = $A4<>$A3. It didn't have an affect.

I say all of this with the caveat - I'm not an excel expert by any means.
All of this may be an issue with my limited knowledge of the product.

"Jim Thomlinson" wrote:

> I would be inclined to use conditional formatting based on whether the vlaue
> in column A changes. Format -> Conditional Format -> Formula =$A2<>$A1
> (applied to the entire column)...
> --
> HTH...

Place your cursor in Cell A2:A10 (A2 should be the active cell). Select
Format | Conditional Format. Change from Cell Value Is to Formula Is. Add the
formula =$A2<>$A1. Change the format to Add a underline border. Click Ok...
--
HTH...

Jim Thomlinson


"Orlanzo" wrote:

> Jim,
> I applied the formula, not to the entire column, but to the first column of
> the rows comprising the range. It had no affect. The range begins at A3, so
> I modified the formula to erad = $A4<>$A3. It didn't have an affect.
>
> I say all of this with the caveat - I'm not an excel expert by any means.
> All of this may be an issue with my limited knowledge of the product.
>
> "Jim Thomlinson" wrote:
>
> > I would be inclined to use conditional formatting based on whether the vlaue
> > in column A changes. Format -> Conditional Format -> Formula =$A2<>$A1
> > (applied to the entire column)...
> > --
> > HTH...
>