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.
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. Then open the Visual Basic Editor 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.
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 = "Tahoma"
.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 would like to extend this Macro and need our help then use our Contact Us form and let us know what you want to acheive….If you found this Macro helpful please leave a comment for our development team.
{ 0 comments… add one now }