excel - Change a color of random cell in a worksheet

Question

I want to randomly choose a cell and paint it in a color, but I don't know how can I randomly choose a cell, I searched In Google but I didn't find anything useful, I've tried all the examples given by google but nothing works.

I want to save the random generated cell inside a variable and then be able to change its color, value and etc. How can we do that?

Sub RandomCell()
    x = ' Random Cell '
    Range(x).Interior.Color = RGB(255, 0, 0) ' Red '
End Sub

Or maybe even return the cell that was generated

Function RandomCell() As Cell 'What is the data type for a cell?`
    x = ' Random Cell '
    Range(x).Interior.Color = RGB(255, 0, 0) ' Red '

    RandomCell = x
End Function

question from:https://stackoverflow.com/questions/65939936/change-a-color-of-random-cell-in-a-worksheet

Answer 1

Use the rnd function to determine the row and column.

The formula is Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

So assuming you can see roughly 25 rows and 25 columns it would be:

    Dim r As Long
    Dim c As Long
    
    r = Int((25 * Rnd) + 1)
    c = Int((25 * Rnd) + 1)
    Debug.Print "row: " & r, "column: " & c
    Cells(r, c).Interior.Color = vbRed

Returning a cell:

Function randomizecell() As Range
    Dim r As Long
    Dim c As Long
    
    r = Int((25 * Rnd) + 1)
    c = Int((25 * Rnd) + 1)
    
    Set randomizecell = Cells(r, c)
End Function

Sub colorcell()
    Dim cell As Range
    
    Set cell = randomizecell()
    cell.Interior.Color = vbRed
End Sub