Home |
Search |
Today's Posts |
#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("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 |
#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("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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
only last cell on page to have bottom border (cell area outline) | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
outside cell border | Excel Discussion (Misc queries) |