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.