Use an Excel VBA Macro to Select and Color Random Cells
Question details
Select and color a specified number of unique random cells within C3:AH52 using 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.
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.
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.
In Excel, open Developer > Visual Basic. In the VBA editor, select Insert > Module.
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
Change numCells = 10 to the number of cells you want. You can also change C3:AH52 or replace vbYellow with another VBA color.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Activate the target worksheet, then choose Developer > Macros, select ColorRandomCells, and click Run.

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. Open the workbook: Open the workbook in WPS Spreadsheets and activate the worksheet containing the target range.
- 2. Open the VBA editor: Use Developer > Visual Basic if it is available in your WPS edition, then select Insert > Module.
- 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. Save and run: Save the file in .xlsm format, then use Developer > Macros to select and run ColorRandomCells.

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.




