VBA & Macro Problems

Use an Excel VBA Macro to Select and Color Random Cells

Nimra MalikNimra Malik Nov 20, 2026 869 views

Question details

Select and color a specified number of unique random cells within C3:AH52 using VBA.

Select and Color Random Excel Cells with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants a macro that randomly selects cells from a defined worksheet range and applies a fill color.
Observed behavior
The desired result is a set of randomly selected, yellow-filled cells with no duplicate picks.
Before you start

Open the workbook and activate the worksheet containing C3:AH52. Make sure you can access the VBA editor and save the workbook as a macro-enabled file.

Solution 1Recommended

Run a VBA macro to select and color unique random cells

Use this macro to pick a configurable number of cells without selecting any cell twice.

1
Open the VBA editor

In Excel, open Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the macro

Paste this code into the module: Sub ColorRandomCells() Dim target As Range, picked As Range, cell As Range Dim indices() As Long Dim numCells As Long, total As Long Dim i As Long, j As Long, temp As Long Set target = ActiveSheet.Range("C3:AH52") numCells = 10 total = target.Cells.Count If numCells < 1 Or numCells > total Then MsgBox "Choose a number between 1 and " & total & "." Exit Sub End If ReDim indices(1 To total) For i = 1 To total indices(i) = i Next i Randomize For i = 1 To numCells j = i + Int(Rnd() * (total - i + 1)) temp = indices(i) indices(i) = indices(j) indices(j) = temp Set cell = target.Cells(indices(i)) cell.Interior.Color = vbYellow If picked Is Nothing Then Set picked = cell Else Set picked = Union(picked, cell) End If Next i picked.Select End Sub

3
Set the cell count

Change numCells = 10 to the number of cells you want. You can also change C3:AH52 or replace vbYellow with another VBA color.

4
Save and run

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Activate the target worksheet, then choose Developer > Macros, select ColorRandomCells, and click Run.

Run a VBA macro to select and color unique random cells
Existing fills remain: The macro does not clear previous fill colors. Running it again may leave more colored cells than the number specified for the latest run.
Work with spreadsheets in WPS Office

Color random spreadsheet cells with a VBA macro in WPS

If your desktop edition of WPS Spreadsheets supports VBA and has the VBA component installed, use the same macro to select and color unique random cells.

  1. 1. Open the workbook: Open the workbook in WPS Spreadsheets and activate the worksheet containing the target range.
  2. 2. Open the VBA editor: Use Developer > Visual Basic if it is available in your WPS edition, then select Insert > Module.
  3. 3. Add and adjust the macro: Paste the ColorRandomCells macro from the solution above into the module and set numCells, the range, and the color as needed.
  4. 4. Save and run: Save the file in .xlsm format, then use Developer > Macros to select and run ColorRandomCells.
Select a configurable number of random cells and apply a fill colorOpen and save Microsoft Excel .xlsx and .xlsm formatsWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How do I change how many cells the macro colors?

In the VBA module, change numCells = 10 to your preferred count. The count must be at least 1 and no greater than the number of cells in the target range.

Can the macro pick the same cell twice?

No. It shuffles cell indices as it picks them, so each cell is selected at most once per run.

How do I use a different range or color?

Change ActiveSheet.Range("C3:AH52") to your desired range and replace vbYellow with another VBA color, such as vbRed.

Why are old colored cells still visible after I run the macro again?

The macro adds a yellow fill to the new picks but does not remove existing fills. To clear them manually, select the relevant cells and choose Home > Fill Color > No Fill before running it again.

Can I run this macro in WPS Spreadsheets?

You can use it in a desktop WPS edition that supports VBA and has the VBA component installed. Check whether Developer > Visual Basic is available before adding the macro.