At this point, you can keep the default range setting and click OK. The macro accommodates the active range by setting it as the input box's default input range, as you can see in Figure D. You can select the range before or after executing the macro. This second macro is similar to the first, but better because it's flexible. 'Quit sub procedure when user clicks InputBox Cancel button.Įlse: MsgBox "Error: " & Err.Number, vbOKĮnter Appl圜olor2 into the VBE and run it the same way you did Appl圜olor1. 'Do something to the selected or input range. Set selectedRng = Application.InputBox("Range",, selectedRng.Address, Type:=8) 'Apply RGB(255,255,0) to selected or input range. It also accommodates users who select the range before executing the macro. It allows users to type a range or a range name or select the range after executing the macro. The macro is Listing B is a bit more user friendly. Users won't always want to select the range before executing the macro selecting large or multiple ranges can be awkward. SEE: 20 quick Windows tips for power users (free TechRepublic PDF) Act on an input range selection Figure C The macro highlights the selected ranges. 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. 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. Figure B Run the macro Appl圜olor1.Īs you can see in Figure C, the macro applies the fill color to only the selected range.
Select the range where you want to apply the fill color yellow-for instance, B4:H4 and B16:H16 ( Figure A).You'll find both macros on the QAT and in a custom Macros group on the Home tab in the demonstration.
#Excel vba how to#
That article will also show you how to display the Developer tab if necessary. If you don't know how, read How to add Office macros to the QAT toolbar for quick access. To easily access the macro, add the macro to the Quick Access Toolbar or a custom group on the Ribbon. If the cell or range contains data, don't worry. If you're using a Ribbon version of Excel, save the workbook as a macro-enabled workbook before continuing.īefore executing this macro, select a single cell or a contiguous or noncontiguous range.
Don't copy from this web page because the VBE can't interpret special web characters and will return an error. You can import the macro from the downloadable.
#Excel vba code#
To add the code to a workbook, press F11 to launch the VBE. Unfortunately, you can't remove the fill color by pressing +Z or clicking Undo. Listing A changes the fill color of the selected range to yellow, but you can do most anything. That's it! Once you define the Range object, you can act upon it. The Set statement defines the Range using the existing selection, provided by the Application object's Selection property. The Dim statement declares a Range object named selectedRng. In this case, the Dim and Set statements do the real work: Dim selectedRng As Range To use VBA to act upon an existing selection, you need to create a Range object and set that object to the existing range.
#Excel vba pro#
SEE: Enduser data backup policy (Tech Pro Research) Act on an existing selection If you've never created a macro, you should be able to apply the steps in this article to completion. Excel's browser version doesn't support macros.
#Excel vba download#
You can work with your own workbook or download the demonstration.
#Excel vba windows 10#
I'm using Office 365 (Excel 2016 desktop) on a Windows 10 64-bit system, but these macros will work in older versions.
0 kommentar(er)
