Vba with keyword in Excel

There are some occassions while programming, where one would want to refer an object repeatedly for some purpose.

For example, if there is a need to change the font,color, backcolor and so on for a range object, then the same object has to be repeated for every property setting.

But with "With..End With" statement, the object is referred only once at the beginning and the rest of the code is automatically made to refer that object only.

This feature comes handy while coding with multiple objects, wihout which, the coding will be tedious.

Also note that, When using a structure, you can only read the values of members or invoke methods, and you get an error if you try to assign values to members of a structure used in a "With End with" block.


  1. With
  2. End With
  3. The following example demonstrates the use of with statement.

    1. Sub withendeith()
    2. Dim rng As Range
    3. Set rng = Sheets("Q238").Range("K12:K22")
    4. With rng
    5. .Interior.ColorIndex = 37
    6. End With
    7. MsgBox "K12:K22 background changed with \'With\' Statement"
    8. rng.Select
    9. With Selection.Interior
    10. .Pattern = xlSolid
    11. .PatternColorIndex = xlAutomatic
    12. .ThemeColor = xlThemeColorDark1
    13. .TintAndShade = 0
    14. .PatternTintAndShade = 0
    15. End With
    16. End Sub

    The above example changes the background color, theme color, patterncolorindex,tint and shade and so on of the range K12:K22.

    Note the preceeding .(dot) within the "With" block. Without this, the rng object has to be called at each statement.

    The Screenshot of the editor is as shown below

    excel vba with keyword


    You can find similar Excel Questions and Answer hereunder

    1) How can I clear cell after activating a routine when there is a change in value of a cell?

    2) Line break in vba message box in Excel

    3) Excel 2010 vba replacement of application filesearch in Excel

    4) The scenario manager in Excel VBA allows to explore various scenarios in a very easy way

    5) How to add a link in a sheet to another sheet

    6) Here some explanations about the MSXML who stands for Microsoft XML core services

    7) How can I get input from user through a prompt and assign user's input to a cell?

    8) How to read a value from a cell in vba in Excel

    9) How can I export a chart as a gif file?

    10) How can I list all files in a folder using VBA?


    Here the previous and next chapter