Using With Statements
Last modified: December 06, 2011
Applies to: Office 2013 | VBA
The following example fills a range of cells with the number 30, applies bold formatting, and sets the interior color of the cells to yellow.
Sub FormatRange() With Worksheets("Sheet1").Range("A1:C10") .Value = 30 .Font.Bold = True .Interior.Color = RGB(255, 255, 0) End With End Sub
You can nest With statements for greater efficiency. The following example inserts a formula into cell A1, and then formats the font.
Sub MyInput() With Workbooks("Book1").Worksheets("Sheet1").Cells(1, 1) .Formula = "=SQRT(50)" With .Font .Name = "Arial" .Bold = True .Size = 8 End With End With End Sub