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
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.
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 Loop i = i + 1 Loop 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.