Below we will look at a program in Excel VBA that compares randomly selected ranges and highlights cells that are unique. If you are not familiar with areas yet, we highly recommend you to read this example first. Situation: Note: the only unique value in this example is the 3 since all other values occur […]
This example illustrates the Areas collection in Excel VBA. Below we have bordered Range(“B2:C3,C5:E5”). This range has two areas. The comma separates the two areas. Place a command button on your worksheet and add the following code lines: 1. First, we declare two Range objects. We call the Range objects rangeToUse and singleArea. Dim rangeToUse […]
Changing background colors in Excel VBA is easy. Use the Interior property to return an Interior object. Then use the ColorIndex property of the Interior object to set the background color of a cell. Place three command buttons on your worksheet and add the following code lines: 1. The code line below sets the background […]
The Font property of the Range object in Excel VBA gives access to a lot of other properties. That is because the Font property returns an object itself; the Font object. The Font object has many properties like the Color property and the Bold property. Color property To change the color of an Excel range, […]
Below we will look at a program in Excel VBA that shows a print preview of all the possible football matches from a list of teams. Situation: 1. First, we declare one Range object and four variables. We call the Range object rng. One String variable we call matchname, and three Integer variables we call […]
This program in Excel VBA uses the Count property, IsNumeric function, IsEmpty function and Intersect method to test a selection. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare two Range objects. We call the Range objects rng and cell. Dim rng As Range, cell As […]
The Union method in Excel VBA returns a Range object that represents the union of two or more ranges (borders below for illustration only). Code line: Union(Range(“B2:C7”), Range(“C6:F8”)).Select Result: Note: the Union method doesn’t return the mathematical union (cell C6 and cell C7 are included twice). The Intersect method in Excel VBA returns a Range […]
This example illustrates the End property of the Range object in Excel VBA. We will use this property to select the range from the Active Cell to the last entry in a column. Situation: Some sales figures in column A. Assume that you will be adding more sales figures over time. Place a command button […]
The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range (border below for illustration only). Place a command button on your worksheet and add the following code lines: Dim example As RangeSet example = Range(“A1:A2”)example.Offset(3, 2).Select Result when you click the command […]
This example teaches you how to select entire rows and columns in Excel VBA. Are you ready? Place a command button on your worksheet and add the following code lines: 1. The following code line selects the entire sheet. Cells.Select Note: because we placed our command button on the first worksheet, this code line selects […]
The Resize property in Excel VBA makes a range (border below for illustration only) a specific number of rows and columns larger or smaller. The Resize property always takes the top left cell of a range as the starting point. Code line: Range(“A1:C4”).Resize(3, 2).Select Result: Explanation: this code line resizes Range(“A1:C4”) to 3 rows and […]
Below we will look at a program in Excel VBA that colors the maximum value of a dynamic range. Situation: Each time we add a number and we click the command button, we want Excel VBA to color the maximum value of these numbers. Place a command button on your worksheet and add the following […]
This example illustrates the CurrentRegion property in Excel VBA. The current region is a range bounded by any combination of blank rows and blank columns. Can you find the current region of cell A1? Place a command button on your worksheet and add the following code line: Range(“A1”).CurrentRegion.Select Result when you click the command button […]
