highlighting a block of data

[brian kaufmann]

Is there a way to create a macro in an excel worksheet which could do
the following:

- someone can position the cursor anywhere on the excel worksheet
- macro checks all rows in that columns above where the cursor is
located and below where the cursor is located.
It locates any cells above and below the cursor cell where there is line
formatting. It highlights in blue all of the cells in that column
circumscribed at the top by a line and circumscribed at the bottom by a
line (highlights in blue the block of data). The lines were created by:

format cells
preset: outline
border: line at top of cell
line at bottom of cell
line style: default setting

Basically, the macro would highlight in blue the block of data in the
block where the person positions the cursor.

For example, if the data looked like this:
____
AA
BB
CC
DD
____

If the person put the cursor on the cell containing BB, the macro would
highlight all the rows in that column where the cursor is which are
below the top line and above the bottom line (the whole block of data
cells containing AA,BB,CC,DD,empty cell).

Thanks.

Brian Kaufmann

 

[Tom Ogilvy]

Sub AABB()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
With ActiveSheet.UsedRange
Set rng2 = .Rows(.Rows.Count)
End With
Set rng = ActiveCell
Do While rng.Borders(xlTop).LineStyle = xlNone
Set rng = rng.Offset(-1, 0)
If rng.Row = 1 Then Exit Do
Loop
Set rng1 = ActiveCell
Do While rng1.Borders(xlBottom).LineStyle = xlNone
Set rng1 = rng1.Offset(1, 0)
If rng1.Row = rng2.Row Then Exit Do
Loop
Range(rng, rng1).Interior.ColorIndex = 5

End Sub

 

[brian kaufmann]

Thanks Tom. -- Brian