Home / Formatting / Change Cell Formats using Ranges and Offset

Change Cell Formats using Ranges and Offset

The following Macro has been designed to read through a range of cells and update the Cell formats for Font, Color, Borders and Background Color. This type of Macro can be really useful in worksheet updates where inserts, updates and deletion of cells can result in a loss of formatting for the worksheet. With this Macro the formats can be re-applied with one command after all the updates are made.

Change Cell Formats

Step 1

Create a sample workbook like the one below with Headings beginning in Row 4 and some basic data in 4 columns across the worksheet. Don’t include any colours or borders in your sample workbook.

workbook sample

Step 2

Open the Visual Basic Editor (Alt + F11) for the workbook and copy the Macro below into the editor. Run the Macro and you should see the formats applied like the example below. Change the font, size and colours in the Macro to see what different effects can be acheived.

Sub FormatRangeOffsetExample()
'Routine to update formatting using ranges

Dim myRange                             As Range
Dim i                                   As Integer
Dim j                                   As Integer
    Set myRange = Worksheets("Sheet1").Range("A4")
    i = 1
        Do While myRange.Offset(i, 0).Value <> ""
            j = 0
            Do While myRange.Offset(0, j).Value <> ""
            'Set the borders for the formatted region
            With myRange.Offset(i, j).Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            'Set the standard format for the region
            With myRange.Offset(i, j)
                .Font.Name = "Arial"
                .Font.Size = 12
            End With
            'Set the colour formatting for some of the cells in the region
            If myRange.Offset(i, j).Value = "High" Then
                myRange.Offset(i, j).Font.ColorIndex = 3
                myRange.Offset(i, j).Interior.ColorIndex = 6
            End If
            j = j + 1
        i = i + 1
End Sub

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk