Question

(EXCEL) On a new sheet called Hello, insert numbers into the range A1: E10. The following code is used to format some data. This code works perfectly well, but it is quite repetitive. Rewrite it by using as many with constructions as make sense. Using appropriate indentation and then run your modified code to make sure it still works.

Sub FormatARange()

ActiveWorkbook.Worksheets("Hello").Range("B1").Font.Bold = True ActiveWorkbook.Worksheets("Hello").Range("B1").Font.Size = 14 ActiveWorkbook.Worksheets("Hello").Range("B1").Interior.Color = vbYellow ActiveWorkbook.Worksheets("Hello").Range("B1").HorizontalAlignment = xlLeft ActiveWorkbook.Worksheets("Hello").Range("A3:A8").Font.Bold = True ActiveWorkbook.Worksheets("Hello").Range("A3:A8").Font.Italic = True ActiveWorkbook.Worksheets("Hello").Range("A3:A8").Interior.Color = vbGreen ActiveWorkbook.Worksheets("Hello").Range("A3:A8").InsertIndent 1 ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Font.Bold = True ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Font.Italic = True ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Font.Color = vbYellow ActiveWorkbook.Worksheets("Hello").Range("B2:E2").Interior.Color = vbBlue ActiveWorkbook.Worksheets("Hello").Range("B2:E2").HorizontalAlignment = xlRight ActiveWorkbook.Worksheets("Hello").Range("B3:E8").Interior.Color = vbRed ActiveWorkbook.Worksheets("Hello").Range("B3:E8").NumberFormat = "$#,##0"

End Sub

Answer 1

VBA code:

Sub FormatARange()
Dim sheet As Worksheet
Set sheet = Sheets("Hello")
With sheet.Range("B1")
.Font.Bold = True
.Font.Size = 14
.Interior.Color = vbYellow
.HorizontalAlignment = xlLeft
End With
With sheet.Range("A3:A8")
.Font.Bold = True
.Font.Italic = True
.Interior.Color = vbGreen
.InsertIndent 1
End With
With sheet.Range("B2:E2")
.Font.Bold = True
.Font.Italic = True
.Font.Color = vbYellow
.Interior.Color = vbBlue
.HorizontalAlignment = xlRight
End With
With sheet.Range("B3:E8")
.Interior.Color = vbRed
.NumberFormat = "$#,##0"
End With
End Sub

Sample output:

Explanation:

Set sheet = Sheets("Hello")

assiging worksheet Hello to variable sheet. Hereafter we can refer worksheet hello by variable name sheet. so we don't want to repeatedly use ActiveWorkbook.Worksheets("Hello").

With Statement

The key feature of the with the statement is that we can access the properties of the object without referring to the object multiple times. The method to access the properties of the object lies in the dot keyword. When we put a dot keyword we can see a multiple number of options to access the properties of the object.