Cell Border

If I sort data in a column, most of the cell properties move with the values.
For example, if the value 1 moves to the top of the column, it retains the
same font and background color that it had in its original position. This is
not true of the border, how can I make the border move with the value?
--
Gary's Student

GS,

As far as I know, you will have to do it using code.

The following code assumes only one column will be sorted
and that RngOne is the data to sort and RngTwo a copy of RngOne.
(I'll let you write the code to copy the range)
In the example, I've specified fixed ranges.
The range should be copied before the sort and
the example code run after the sort.

Jim Cone
San Francisco, USA


'----------------------------
Sub TransferCellFormats()
Dim RngOne As Excel.Range
Dim RngTwo As Excel.Range
Dim lngN As Long
Dim lngPos As Long

Set RngOne = Range("B5:B14")
Set RngTwo = Range("D5:D14")

For lngN = 1 To RngOne.Count
lngPos = Application.Match(RngOne(lngN).Value, RngTwo, 0)
RngTwo(lngPos).Copy
RngOne(lngN).PasteSpecial Paste:=(-4122) 'xlPasteFormats
Next 'lngN
Application.CutCopyMode = False
Set RngOne = Nothing
Set RngTwo = Nothing
End Sub
'-------------------------------



"Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:A8FEBC9E-BFB0-4ECC-9B9A-3DE52417E9AB@microsoft.com...
If I sort data in a column, most of the cell properties move with the values.
For example, if the value 1 moves to the top of the column, it retains the
same font and background color that it had in its original position. This is
not true of the border, how can I make the border move with the value?
--
Gary's Student

Thank you very much for your help
--
Gary's Student


"Jim Cone" wrote:

> GS,
>
> As far as I know, you will have to do it using code.
>
> The following code assumes only one column will be sorted
> and that RngOne is the data to sort and RngTwo a copy of RngOne.
> (I'll let you write the code to copy the range)
> In the example, I've specified fixed ranges.
> The range should be copied before the sort and
> the example code run after the sort.
>
> Jim Cone
> San Francisco, USA
>
>
> '----------------------------
> Sub TransferCellFormats()
> Dim RngOne As Excel.Range
> Dim RngTwo As Excel.Range
> Dim lngN As Long
> Dim lngPos As Long
>
> Set RngOne = Range("B5:B14")
> Set RngTwo = Range("D5:D14")
>
> For lngN = 1 To RngOne.Count
> lngPos = Application.Match(RngOne(lngN).Value, RngTwo, 0)
> RngTwo(lngPos).Copy
> RngOne(lngN).PasteSpecial Paste:=(-4122) 'xlPasteFormats
> Next 'lngN
> Application.CutCopyMode = False
> Set RngOne = Nothing
> Set RngTwo = Nothing
> End Sub
> '-------------------------------
>
>
>
> "Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:A8FEBC9E-BFB0-4ECC-9B9A-3DE52417E9AB@microsoft.com...
> If I sort data in a column, most of the cell properties move with the values.
> For example, if the value 1 moves to the top of the column, it retains the
> same font and background color that it had in its original position. This is
> not true of the border, how can I make the border move with the value?
> --
> Gary's Student
>