If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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("D514") 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 |
#3
|
|||
|
|||
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("D514") 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Change border color of active cell?? | stigva | General Discussion | 0 | November 3rd, 2004 02:14 PM |
Changing Cell References | Katrina | Worksheet Functions | 1 | September 22nd, 2004 05:37 PM |
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
using cell contents in pathname | Don Guillett | Worksheet Functions | 7 | January 2nd, 2004 02:11 PM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |