Pick random cell in VBA

[Flammy]

Hi there I'm trying to pick a random cell in a range (A1:BW30) and change the background color of those cells. This would then be repeated say 50 times. There can be repetition in the cells picked but if its easy to prevent repetition that would be awesome. Ideally this would all be in a sub procedure. I tried modifying some code from here: http://www.access-programmers.co.uk/...d.php?t=161420 but I kept getting an invalid range error... I also looked around and there seemed to be lots of good advice which barely didn't fit what I was looking for.

Thanks in advance!

 

[poolhall]

Adapted from here: http://www.pcreview.co.uk/forums/vba-pick-random-cell-print-t1054551.html

Function RandCell(Rg As Range) As Range
    Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function

Sub RandCellTest()
Dim Counter As Long
Dim TargetRg As Range, Cell As Range

Set TargetRg = Range("A1:BW30")

TargetRg.ClearFormats

For Counter = 1 To 50
    Set Cell = RandCell(TargetRg)
'    Cell.Interior.Color = RGB(0, 255, 0)   'use this if you need one color
    Cell.Interior.Color = RGB(Int((255 * Rnd)), Int((255 * Rnd)), Int((255 * Rnd)))     'use this if you need random colors
Next

End Sub

 

[Flammy]

That works great with one problem, but not your fault... it resets the formatting on the other cells within that range that it is not affecting (reapplies default boarders and resets to default color...) is there a fix to that to prevent that from happening?

The cells are black with no boarders, if it matters.

Thanks for the help.

 

[poolhall]

Flammy, just comment out the TargetRg.ClearFormats line.

 

[Flammy]

Oh, duh. Thanks, works great.

 

[stephexcel2004]

Hi. Poolhall. The vba you provided work great. But, if I want to randomly highlight 10 cells for each column (let say Column A- A2:A100), and I got thousands of such columns. Do you have any idea?
Thanks in advance.