Using With Statements
The With statement lets you specify an object or user-defined type once for an entire series of statements. With statements make your procedures run faster and help you avoid repetitive typing.
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.
.Value = 30
.Font.Bold = True
.Interior.Color = RGB(255, 255, 0)
You can nest With statements for greater efficiency. The following example inserts a formula into cell A1, and then formats the font.
With Workbooks("Book1").Worksheets("Sheet1").Cells(1, 1)
.Formula = "=SQRT(50)"
.Name = "Arial"
.Bold = True
.Size = 8