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" wrote in message
...
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" wrote in message
...
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