Copying cell border formatting, without shading.

[Jamie]

Hi guys,

We use excel to design layouts for some software, and this requires a rather
complex screen layout that gets emailed to many people. As such, when it's
amended by other teams, they seem to like breaking the formatting...

Is there a way that I can copy the borders of a cell, without taking the
contents (text) of shading that is already in place?

I'd really appreciate some help with this! My life would be SO much easier!

 

[John Keith]

Have you tried using Copy then Paste-Special (choosing "Formats")

Shading and text color will still get pasted, but the text/number values are
not copied. Then select the area(s) just pasted and turn off the
color/hilighting.

This whole process could be "recorded" into a macro to clean-up after your
other teams.

Depending on how your cells are formatted, you could re-assign the "correct"
format as part of the process too (since "Formats" applies to much more then
just the borders)

 

[Jamie]

Hi John,

The problem is, I can't afford to overwrite any formatting of a cell. The
plan was to copy the formatting of a 'sample' cell, and paste it across the
data that comes from other teams.

It's only the bordering that needs to be included, changing the colours of
existing cells is exactly what I don't want to do.

Any clues?

Many thanks for your help this far.

 

[John Keith]

Try adding some VBA code that you can run via(alt-F8) from the worksheet.
Using the record-macro feature as you select a sample cell and apply the
bordering. It will create MOST of the code like below. I renamed Macro1,
Macro2 etc. to Border1... Add the necessary ActiveSheet.Range("xxxxx") values
needed to define the ranges where you are applying the border-format.

This will set the borders leaving data, colors, text/number formatting alone.

Below is some example VBA code to put in a Visual Basic module. (if this
code gets stored with the master and is emailed out, the Security settings
will complain, but it should be ok for all your users to disable macros.
(when you open the workbook, you will have to enable macros to allow running
Set_Borders.)

Option Explicit
Sub Set_Borders()
' Call the Border# subroutine with the range(s) defined for the areas
' on the worksheet needing the border set
Border1 (ActiveSheet.Range("A1:A10"))
Border2 (ActiveSheet.Range("B1:B10"))
Border1 (ActiveSheet.Range("C1:C10"))
End Sub
' Record macros as you define each sample cell's style of borders
' Modify the "Selection." code to "target." (like below)
' Add "(target as range)" onto the macro# that was recorded
' Put the code for each cell into a seperate SUB
' The "Select.Range("F11")" statements should be deleted
Sub Border1(target As Range)
' Set thin border around all cells in range
On Error Resume Next
target.Borders(xlDiagonalDown).LineStyle = xlNone
target.Borders(xlDiagonalUp).LineStyle = xlNone
With target.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With target.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With target.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With target.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With target.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With target.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
On Error GoTo 0
End Sub
Sub Border2(target As Range)
' Set medium border around outside of range
On Error Resume Next
target.Borders(xlDiagonalDown).LineStyle = xlNone
target.Borders(xlDiagonalUp).LineStyle = xlNone
With target.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With target.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With target.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With target.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
On Error GoTo 0
End Sub