Borders to stay put!

  • Thread starter Thread starter nikwak
  • Start date Start date
N

nikwak

I am writing a template for someone and they want to have the dotted line
around each cell. However, when they get hold of the template, what they are
doing is inserting more lines and deleting certain cells and they are losing
the dotted formatting on certain cells that have had the contents deleted.
Basically - is there a way to make the border formatting stick no matter
what happens to the content of the cell so that there will be a dotted
border line round all cells no matter what?
nikwak
 
You may be able to have a macro that reapplies your border, but it may be
simpler to find out what the users are doing.

It sounds like they're clearing the cells (edit|clear|all) instead of clearing
the contents (hitting the delete key or edit|clear|contents).

An event macro approach that may work for you:

First, select your range of cells that should have the same border. Then give
it a nice name in the Namebox (to the left of the formula bar). I used
myFormatRng in my example.

Then rightclick on the worksheet tab that should have this behavior and select
view code. Paste this in.

Each time the user changes the selection, the borders will be reapplied to each
cell in that range.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("myFormatRng")
For Each myCell In myRng.Cells
myCell.BorderAround LineStyle:=xlDash, _
Weight:=xlHairline, ColorIndex:=3
Next myCell

End Sub
 
Back
Top