How to randomly add background/fill color for cells in Excel?

Using VBA (Visual Basic for Applications), you can automatically assign a random fill color to each cell in a selected range. This is helpful when you want each cell to have a different color and you need to repeat this action frequently for different datasets.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.

2. In the VBA editor, click Insert > Module to create a new module.

3. Copy and paste the following VBA code into the module window (do not edit the code):

VBA: Add background or fill color for cells with random color

Sub TrimExcessSpaces()
Dim rng As Range
Dim WorkRng As Range
Dim xRed As Byte
Dim xGreen As Byte
Dim xBule As Byte
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each rng In WorkRng
xRed = Application.WorksheetFunction.RandBetween(0, 255)
xGreen = Application.WorksheetFunction.RandBetween(0, 255)
xBule = Application.WorksheetFunction.RandBetween(0, 255)
rng.Pattern = xlSolid
rng.PatternColorIndex = xlAutomatic
rng.Interior.Color = VBA.RGB(xRed, xGreen, xBule)
Next
End Sub

4. Press F5 or click the Run button to execute the macro.

5. When prompted, select the cell range you want to fill with random colors, and click OK.

6. The selected cells will be filled with randomly generated colors. To repeat for another range, run the macro again and select a new range.

In many practical scenarios, you may prefer to randomly select just a subset of cells rather than the entire range, and then apply the background color of your choice. Kutools for Excel offers a user-friendly utility named Sort Range Randomly that simplifies this task without the need for VBA or formulas. This is especially helpful for users who want to add a splash of color to only a certain number of cells in a list or dataset — for instance, to highlight randomly selected winners, assign groups, or sample test data.

1. Select the entire range of cells where you want to randomly fill some with background color. For best results, ensure your data does not contain merged cells or other formatting that could affect selection.

2. Go to the Kutools tab on the ribbon, choose Range, then click Sort Range Randomly.

3. In the dialog box, switch to the Select tab. Enter the number of cells you wish to select at random in the No. of cell(s) to select box, and check Select random cells. Click OK to confirm.

4. The specified number of cells will be highlighted (selected). To apply a fill color, go to the Home tab, click the arrow next to Fill Color, and choose a color. The selected cells will be colored immediately.

This method is ideal if you wish to control the quantity of randomly colored cells and prefer to select your fill color. Kutools' approach avoids the need to write any code or use formulas, making the process accessible even to users less familiar with Excel's more advanced features. The main limitation is that it only assigns a single chosen color at a time; for fully randomized coloring of all cells, the VBA approach described earlier may be more suitable. If you need to re-select or change random cells, simply repeat the selection process using Sort Range Randomly, then apply a new color as needed.

Randomly sort cells in a column or range

Select cells randomly

Generate random strings