Table of Contents

Dynamic Range

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.

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

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog