Customized Borders

[waseem]

While doing my routine work on excel, my fav formatting style is that first i fill entire sheet with lightest grey shade, then I draw borders of cells in 3D style. For example for upper and left border I select dark grey color and white for lower and right border. Thats how I get an ENGRAVED 3D effect.


The problem is that I have to do that all the times, i.e. go to "format cell>Borders" and then apply this border style. Is there a way to make this style my default arrangement? I tried it using macros but it works in the same workbook. I do not know how to write macros so cannot do programming sort of things. Can someone plz help me knowing how I can set a particular border style as default for all ranges that I select in future?

 

[Hui]

Waseem

The following Macro will apply your style to the area selected

It can be a single cell or a Range

Copy and Paste the code into a Module in VBA Alt F11

You can then apply the code to a Toolbar Button, Macr Button or Shape on the Worksheet

You will still have to apply the Background Grey to the whole sheet manually

[pre]

Sub Waseem()
'
' Waseem Macro
' @ Chandoo.org
'
' by
' Hui...
' July 2010
'

With Selection.Interior 'Interior Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
With Selection.Borders(xlEdgeLeft) 'Left Border
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0.349986266670736
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop) 'Top Border
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0.349986266670736
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom) 'Bottom Border
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight) 'Right Border
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone 'Inside V.Lines
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 'Inside H.Lines

End Sub

[/pre]

 

[waseem]

Thanks a million Hui. You are always there to help.

Only a couple more issues. The background color that I need is laightest grey shade and the line thickness also needs to be minimum. Would I be able to save this macro be usable in all excel workbooks or just in one file? Should i save the file afterwards or what (in case i do it with a new workbook)?


Thanks again.. cheers

 

[Hui]

Waseem

Try using the Macro Recorder

Do one section at a time

Look at the code and replace the relevent sections as required.

I have changed the Comments above to assist.


The Macro will only be available in the workbook you are using

To make it always available, you can add it to a file called personal.xlsm and then link it to a Button in the Toolbar. Personal.xlsm must be in your XLSTART directory