VBA makes it easy to select an Excel range. Here are two macros that offer maximum efficiency and flexibility.
Using VBA to perform some action on an Excel range is a frequent task, but to do so, you need a way to convey to VBA which cells you want to act upon. Using the Application's Selection property is probably the easiest (but not the only) way. In this article, I'll show you two macros: The first lets a user select a specific range before executing the macro; the second lets a user select the range before or after executing the macro. You can do most anything to the range; I chose to apply a fill color because it's simple and doesn't distract from the article's focus--the selection process.
I'm using Office 365 (Excel 2016 desktop) on a Windows 10 64-bit system, but these macros will work in older versions. You can work with your own workbook or download the demonstration .xlsm, .xls, and .bas file (the code module). Excel's browser version doesn't support macros. If you've never created a macro, you should be able to apply the steps in this article to completion.
SEE: Enduser data backup policy (Tech Pro Research)
Act on an existing selection
To use VBA to act upon an existing selection, you need to create a Range object and set that object to the existing range. It's simpler than it sounds. In this case, the Dim and Set statements do the real work:
Dim selectedRng As Range Set selectedRng = Application.Selection
The Dim statement declares a Range object named selectedRng. The Set statement defines the Range using the existing selection, provided by the Application object's Selection property. That's it! Once you define the Range object, you can act upon it. Listing A changes the fill color of the selected range to yellow, but you can do most anything. Unfortunately, you can't remove the fill color by pressing [Ctrl]+Z or clicking Undo.
Sub ApplyColor1() 'Apply RGB(255,255,0) to selected range. Dim selectedRng As Range 'Error handling; for good measure. On Error Resume Next 'Define selected range. Set selectedRng = Application.Selection 'Do something with the selection. With selectedRng.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 'Same as RGB(255,255,0) .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub
To add the code to a workbook, press F11 to launch the VBE. From the Insert menu, choose Module. Then, add the code. You can import the macro from the downloadable .bas file or type it yourself. Don't copy from this web page because the VBE can't interpret special web characters and will return an error. If you're using a Ribbon version of Excel, save the workbook as a macro-enabled workbook before continuing.
Before executing this macro, select a single cell or a contiguous or noncontiguous range. If the cell or range contains data, don't worry. The macro won't overwrite anything. To easily access the macro, add the macro to the Quick Access Toolbar or a custom group on the Ribbon. If you don't know how, read How to add Office macros to the QAT toolbar for quick access. That article will also show you how to display the Developer tab if necessary. You'll find both macros on the QAT and in a custom Macros group on the Home tab in the demonstration .xlsm file. Here's how we'll run the macro:
- Select the range where you want to apply the fill color yellow--for instance, B4:H4 and B16:H16 (Figure A). To create a noncontiguous range, select either range. Then, hold down the Ctrl key and select the second.
- Click the Developer tab. (Read the article linked above if the Developer tab isn't visible.)
- In the Code group, click Macros.
- In the resulting dialog, select ApplyColor1 (Figure B) and click Run.
Select the cell or range before executing the macro.
Run the macro ApplyColor1.
As you can see in Figure C, the macro applies the fill color to only the selected range. What the macro does is less important than the two statements used to create the Range object from the existing range so you can act upon it. This is super easy, but what happens if you select the wrong range before executing the macro? The next section covers a macro that handles that.
The macro highlights the selected ranges.
SEE: 20 quick Windows tips for power users (free TechRepublic PDF)
Act on an input range selection
Users won't always want to select the range before executing the macro; selecting large or multiple ranges can be awkward. The macro is Listing B is a bit more user friendly. It allows users to type a range or a range name or select the range after executing the macro. It also accommodates users who select the range before executing the macro.
Sub ApplyColor2() 'Apply RGB(255,255,0) to selected or input range. Dim selectedRng As Range Set selectedRng = Application.Selection 'Error handling to capture Cancel key. On Error GoTo errHandler 'Define range. Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8) 'Do something to the selected or input range. With selectedRng.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 'Same as RGB(255,255,0) .TintAndShade = 0 .PatternTintAndShade = 0 End With 'Stop before running error handling. Exit Sub errHandler: 'Quit sub procedure when user clicks InputBox Cancel button. If Err.Number = 424 Then Exit Sub Else: MsgBox "Error: " & Err.Number, vbOK End If End Sub
Enter ApplyColor2 into the VBE and run it the same way you did ApplyColor1. This second macro is similar to the first, but better because it's flexible. You can select the range before or after executing the macro. The macro accommodates the active range by setting it as the input box's default input range, as you can see in Figure D. At this point, you can keep the default range setting and click OK. Or you can enter a new range by typing the range or range name from the keyboard or by selecting the range using your mouse. If you press Cancel instead of OK, the error handler will kick in, and the macro will quit before applying a fill color.
The input box defaults to the range that's selected.
To learn more about referencing ranges, sheets, and even workbooks, read the following articles:.
- Excel tips: How to select cells and ranges efficiently using VBA
- 10 ways to reference Excel workbooks and sheets using VBA
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.