MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Select random cell in list and highlight contents

  Asked By: Carlton    Date: Jan 21    Category: MS Office    Views: 708

Just wondering if anyone can give me ideas for programming code for a macro that
randomly selects a cell, then highlights the cell yellow.

For example, when running the macro first (I will assign it to a command button)
I may have a list of 20 items in column A - I want one of the 20 cells selected
randomly and highlighted yellow. However when running the macro a second time,
I may have added more cells to the list, so I would want it to randomly select
one from the full list (ie not limited to 20 as in the previous example).

The only code I have at the moment is the highlighting part:

Selection.Interior.ColorIndex = 6



3 Answers Found

Answer #1    Answered By: Monique Perry     Answered On: Jan 21

Let's say your list  is in column  A, starting in row 10.
I'd use:
StartRow = 10
RowCnt = Application.WorksheetFunction.CountA(Range("A" & StartRow & ":A65000"))

This will count the number of non-blank cells  in column A from row 10 to 65000
Keep in mind that if there are any blank rows in the middle of the data, it will
count it, and the number will be off.
Next use:

Randomize 'Initializes the
RandRow = Int((RowCnt + 1) * Rnd) + StartRow

this should give  you the Row Number of a random  row in your range.

Can you put it together from here?

Answer #2    Answered By: Roosevelt Jenkins     Answered On: Jan 21

I've got the code  working fairly well, but for
some reason it sometimes highlights the blank cell  after the last cell
containing data. For example, StartRow is 2, and cells  A2:A5 have data in them,
but sometimes cell A6 is highlighted. Any idea what I've done wrong? My code
is as follows:

Sub HighlightRandomCell()
Columns(1).Interior.ColorIndex = 0
StartRow = 2RowCnt = Application.WorksheetFunction.CountA(Range("A" & StartRow &
randrow = Int((RowCnt + 1) * Rnd) + StartRow
Cells(randrow, 1).SelectCells(randrow, 1).Interior.ColorIndex = 6
End Sub

Answer #3    Answered By: Rosie Hughes     Answered On: Jan 21

I suspect it's the "RowCnt + 1".... try using Int(Rowcnt * Rnd) and see how it

Didn't find what you were looking for? Find more on Select random cell in list and highlight contents Or get search suggestion and latest updates.