DMW logo
contact@consultdmw.com

Microsoft® Excel Macro to Fill Cell Backgrounds with Random Colours

Programming in VBA to fill cells in an Excel worksheet with random colours of varying hues and intensities.

Last updated on 2021-01-16 by David Wallis.


Preface

This macro fills a chosen block of cells with different background, or fill, colours. It make use of Excel’s RANDBETWEEN function to randomise the colours.

If you’re learning VBA, then this might be one way of exploring important programming procedures, including the FOR … NEXT loop.

You can adapt the macro to determine the size of the block and to limit the range of hues or saturations, or of both, it uses for the backgrounds.


Check for Availability of RANDBETWEEN Function

The macro relies on the use of the Excel RANDBETWEEN function, which in Excel 2007, 2010, 2013, 2016, 2019 and Microsoft® 365 is included in the Math & Trig list of the Function Library group of Excel's ribbon’s Formulas tab.

If your version of Excel doesn’t list the RANDBETWEEN function amongst the functions available for use, you need to install the Analysis Toolpak before running the macro.

To do this in Excel 2003, for example, choose from the Tools menu the Add-Ins command.


The Background Colour Macro

This is the code of the macro:

Sub sFillColors(rows&, cols&)

On Error GoTo errHandler

Dim r%, g%, b%

Dim iRow&, iCol&

Dim rng As Range, rngFill As Range

Dim msg$, icon&, title$


Set rng = ActiveCell

For iCol& = 0 To cols&

For iRow& = 0 To rows&

Set rngFill = rng.Offset(iRow&, iCol&)

With rngFill.Interior

r% = WorksheetFunction.RandBetween(0, 255)

g% = WorksheetFunction.RandBetween(0, 255)

b% = WorksheetFunction.RandBetween(0, 255)

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = RGB(r%, g%, b%)

End With

Next iRow&

Next iCol&


procDone:

Exit Sub

errHandler:

title$ = "FILL COLOURS ERROR"

icon& = vbOKOnly + vbCritical

msg$ = _

"Please take screen clip of this message." & _

vbNewline & vbNewLine & _

"If not, make note of following details." & _

vbNewline & vbNewLine & _

"Calling Proc: sFillColors" & _

vbNewLine & _

"Error Number: " & Err.Number & _

vbNewLine & _

"Description: " & Err.Description

Resume procDone

MsgBox msg$, icon&, title$

End Sub

When running the macro, the position of the active cell in your worksheet marks the top left-hand corner of the block to which the colours are applied.

For details of the error trapping and error handling included in the macro code follow this link to our page on the subject.


Setting the Range of Cells Coloured

Using the code above, the macro colours a block of cells determined by the values you assign to its arguments rows& and cols&.


Controlling the Range of Colours

The macro tells Excel which colour to use by defining it as a combination of red, green and blue.

The arguments for the RandBetween in each must be integers between 0 and 255.

To make the macro use blue hues alone, and black, change those rows like this:

r% = WorksheetFunction.RandBetween(0, 0)

g% = WorksheetFunction.RandBetween(0, 0)

b% = WorksheetFunction.RandBetween(0, 255)

If you want bright blue hues alone:

r% = WorksheetFunction.RandBetween(0, 0)

g% = WorksheetFunction.RandBetween(0, 0)

b% = WorksheetFunction.RandBetween(212, 255)

This will give fills leaning towards pastel colours:

r% = WorksheetFunction.RandBetween(128, 255)

g% = WorksheetFunction.RandBetween(128, 255)

b% = WorksheetFunction.RandBetween(128, 255)

Leaving the macro as shown, it will use colours chosen from a palette of between 16 and 17 million available to it.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

“Life is about using the whole box of crayons.”

Anonymous