Colour it like crazy

Excel Random Cell Background (Fill) Colours

Here is a macro that fills a chosen block of cells with randomly chosen background, or fill, colours. The position of the active cell in your worksheet marks the top left-hand corner of the block.

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.

If your version of Excel does not 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, from the Tools menu, choose the Add-Ins command.

The Background Colour Macro

This is the code of the macro:

  1. Sub BGColors()
  2. ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. Dim r As Byte, g As Byte, b As Byte
  4. Dim iRow As Integer, iCol As Integer
  5. Dim iRows As Integer, iCols As Integer
  6. Dim rng As Range, rngFill As Range
  7. Dim strMsg As String
  8. Dim iIcon As Integer, strTitle As String
  9.  
  10. On Error GoTo ErrorHandler
  11.  
  12. iRows = 9
  13. iCols = 9
  14. Set rng = ActiveCell
  15.  
  16. For iCol = 0 To iCols
  17. For iRow = 0 To iRows
  18. r = WorksheetFunction.RandBetween(0, 255)
  19. g = WorksheetFunction.RandBetween(0, 255)
  20. b = WorksheetFunction.RandBetween(0, 255)
  21. Set rngFill = rng.Offset(iRow, iCol)
  22. With rngFill.Interior
  23. .Pattern = xlSolid
  24. .PatternColorIndex = xlAutomatic
  25. .Color = RGB(r, g, b)
  26. End With
  27. Next iRow
  28. Next iCol
  29.  
  30. ProcedureDone:
  31. Exit Sub
  32.  
  33. ErrorHandler:
  34. strTitle = "BGColors Macro Error"
  35. iIcon = vbOKOnly + vbExclamation
  36. strMsg = Err.Number & " " & Err.Description
  37. MsgBox strMsg, iIcon, strTitle
  38. Resume ProcedureDone
  39.  
  40. End Sub

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 composing of 10 rows and 10 columns.

If you need to change the size and shape of the block, change the value assigned to the iRows and iCols variables in rows 12 and 13 of the macro.

Controlling the Range of Colours

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

Row 18 of the macro assigns a value to the red component of the colour by assigning a random number to the variable r. Row 19 assigns to variable g the green component; and row 20 to variable b the blue component.

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.