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.
'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
'Set the standard format for the region
With myRange.Offset(i, j)
.Font.Name = "Arial"
.Font.Size = 12
'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
j = j + 1
i = i + 1
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.