If we want to deal with Cell or Range we can directly mention the object and change its properties or call its methods. We can also deal with Cells or Range by simply selecting it. Then we can use Selection object to refer Selected Cells, Range or any other Objects.
50+ Project Management Templates Pack
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Business Presentations Templates Pack
20+ Excel Project Management Pack
20+ PowerPoint Project Management Pack
10+ MS Word Project Management Pack
We select cell range in Excel VBA to do particular task with the selection. For example we may want to change the background color of a range and we may want to change the font to Bold. In this case we do not required to loop each and every cell of the range to change the background color and font.
Select Cell Range in Excel VBA – Solution:
We can use Select method of Range or Cell to select it and do whatever you want to do.
Select Cell Range in Excel VBA – Example:
Following are the various examples which will show you how to select a range and perform some task.
Example to Select a Cell
In this example I am selecting a Cell using Select method of Cell.
Sub sbSelectACell() 'Selecting a Cell Cells(2, 3).Select 'This will select the Cell at 2nd row and 3rd column End Sub
Example to Select a Range
In this example I am selecting a Range using Select method of Range.
Sub sbSelectARange() 'You can also use Range Object Range("C3").Select 'Collection of Cells OR Multiple Cells = Range Range ("B2:C4").Select ' It will Select B2,B3,B4,C2,C3,C4 End Sub
Example to Select a Range and change the background color
In this example I am selecting the range from B2:C4 using Select method. And changing the background color to red using ColorIndex Property of Range.
Sub sbSelectARangeandForamt() 'Selecting a Range Range("B2:C4").Select ' It will Select B2,B3,B4,C2,C3,C4 Selection.Interior.ColorIndex = 3 End Sub
- Open an Excel Workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module from Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute it