Dynamic Range


Below we will look at a program in Excel VBA that colors the maximum value of a dynamic range.


Each time we add a number and we click the command button, we want Excel VBA to color the maximum value of these numbers.

Dynamic Range in Excel VBA

Place a command button on your worksheet and add the following code lines:

1. First, we declare one variable and two Range objects. One variable of type Double we call maximum. We call the Range objects rng and cell.

Dim maximum As Double, rng As Range, cell As Range

2. We add the line which changes the background color of all cells to ‘No Fill’.

Cells.Interior.ColorIndex = 0

3. We initialize rng with the numbers. We use the CurrentRegion property for this. CurrentRegion is useful when we don’t know the exact boundaries of a range in advance.

Set rng = Range(“A1”).CurrentRegion

4. We initialize maximum with the maximum value of the numbers. We use the worksheet function Max to find the maximum value.

maximum = WorksheetFunction.Max(rng)

5. Finally, we color the maximum value. We use a For Each Next Loop.

For Each cell In rng

If cell.Value = maximum Then cell.Interior.ColorIndex = 22

Next cell

Note: instead of ColorIndex number 22 (red), you can use any ColorIndex number.

6. Add a number.

Result when you click the command button on the sheet:

Dynamic Range Result