How do I Preserve Borders?

[BeetleBailey]

When I apply borders to my spreadsheet and then I move cells from one
location to another, the vacated cells become Borderless. Is there a way to
Retain the Borders in the vacated cells w/o having to reapply the borders?

 

[ShaneDevenshire]

Hi,

What's in the cells - formulas, numbers, dates, text, blanks?

 

[robert morris]

This is a information site, not a pulpit

Bob

 

[BeetleBailey]

It depends on the cell. There are formulas in some, times in others and text.

 

[BeetleBailey]

No Preaching here, just a Thought Provoking statement/question.

 

[ShaneDevenshire]

Hi,

You can add the following macro to the sheet object:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("B")
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 55
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 55
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 55
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 55
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 55
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 55
End With
End With
End Sub

To get this to work name the range where the grids should be to B. You can
change the LineStyle, Weight, or ColorIndex to suit your needs.

 

[BeetleBailey]

Thanx Shane for the info. I'd hoped that it was a simple matter of just
"Locking" in a format, but I can see that a Programming Education is required.

I'd appreciate knowing how to do what you what wrote if that won't require
your writing too many volumes of "Excel for Dummies". You can email or PM me
if taht would e better or provide link.

I'll take a look in the Excel Help area and see what I can find in the mean
time.

 

[BeetleBailey]

Shane,

Believe it or not, I figured out how to get the Macro in and running.
HOWEVER...

When I try to cut or copy and paste in the Worksheet, I can't -- Not
anywhere in the sheet. The Paste Option is grayed out (Unavailable). That's
not good.

Any suggestions???

 

[ShaneDevenshire]

Hi,

I interpreted your original question - " I move cells from one
location to another" - to mean you were dragging and dropping the selected
cells.

In that case, make the following little change -
Change the name of the macro to

Private Sub Worksheet_Change(ByVal Target As Range)

 

[BeetleBailey]

Shane,

Once again I say Thanx. Want to make it 3???

If I want to apply this macro to a block of other cells, is it possible to
add more ranges to the macro With Range ("B") or can I redefine the Range to
include other cells???

 

[ShaneDevenshire]

Hi,

A little slow getting back, but had a very busy week. You can define the
range B to be anything you want and you can change the definition anytime you
want. Simply select a desired range(s) and press Ctrl+F3 and retype the name
in the Names in workbook box.