Hi Debbie

Try this routine. Edit to mirror your setup.

Sub Questionaire()
'Leo Heuser, 8 Aug. 2003
Dim BlockOffset As Long
Dim Counter As Long
Dim NumberOfAnswers As Long
Dim NumberOfQuestions As Long
Dim QuestionRange As Range

Set QuestionRange = Worksheets("Sheet3").Range("A2:E7") ' First question
NumberOfQuestions = 120
NumberOfAnswers = 5
BlockOffset = NumberOfAnswers + 2

QuestionRange.Columns(2).EntireColumn. _
Interior.ColorIndex = xlColorIndexNone

For Counter = 1 To NumberOfQuestions
QuestionRange.Cells((Int(Rnd * NumberOfAnswers) + 1) + 1, 2). _
Interior.ColorIndex = 3
Set QuestionRange = QuestionRange.Offset(BlockOffset, 0)
Next Counter

End Sub


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Debbie" skrev i en meddelelse
m...
Hi All,

I'm using XL97 on Win98, and I'm in the process of
creating a test blank. (See example below.)
What I'd like is to be able to run some code to
randomly select one circle in each set of answers
and color that circle red, so that I know to place
the correct answer next to the letter that corresponds
with the red circle.

There will be 120 questions. I gave names to a few of
the ranges, such as Q1Area, Q2Area, Q3Area, and then
I tried using Rand, but I have had no luck. I really
do not want to have to name 120 ranges, but I will,
if necessary.

The question number is in column A, and the question
is to the right of it in merged columns B-E. The
circles are in column B, the corresponding letters
are in column C, and the answers are in column D.
There is a blank row before each question.

I need for the correct answer in each set to change
from test to test (obviously), so that's why I'd like
some sort of random generator. Can anyone help?

Thanks,

Debbie


1. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.

2. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.

3. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.

...

120. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.