Formatting cells with VBA

Formatting cells in VBA isn’t that hard and I thought I’d just show an example of how to format 5 cells in a cross form. If you need help creating a module or opening the editor I refer to a previous post on the subject.

The following sub routine will alter five cells in various way which will show some of the formatting you can do in VBA:

Sub AlteringTheSheet()
 
    'Select Sheet1
    Sheets("Sheet1").Select
 
    'Select cell B2
    Range("B2").Select
 
    '==============B2=====================
    'Set value of B2 to "B2"
    Selection.Value = "B2"
 
 
    '==============B1=====================
    'Move up one step
    Selection.Offset(-1, 0).Select
    'Set value of B1 to B1
    Selection.Value = "B1"
 
 
    '==============A2=====================
    'Move one step left and one down
    Selection.Offset(1, -1).Select
    'Set background color to gray
    Selection.Interior.Color = RGB(240, 240, 240)
    Selection.Value = "A2"
 
 
    '==============C2=====================
    'Move two steps to the right
    Selection.Offset(0, 2).Select
    Selection.Font.Bold = True
 
    'Found a list of colorindexes here:
    'http://dmcritchie.mvps.org/excel/colors.htm
    Selection.Font.ColorIndex = 3 '3 being the color of red...
 
    Selection.Font.name = "Arial"
    Selection.Font.Italic = True
    Selection.Value = "C2"
 
 
    '==============B3=====================
    'Move down one step and left one step
    Selection.Offset(1, -1).Select
 
    'Add a border to the cell
    With Selection.Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Value = "B3"
 
End Sub

This will generate a sheet that looks something like this:

The resulting cross
The resulting cross

Posted in VBA

Leave a Reply