Enter key functions differently after different macro behavior

A

Allen

I posted this last week in the general forum and didn't hear anything.
Since, I believe, it has something to do with the macro, i thought I might
have better luck here in the programming forum.

This is more of an annoyace than a true issue but i would appreciate any help.

I'm running Excel 2007.

I'm using a spreadsheet to keep scores for quiz matches. Across on row 6 I
have the question numbers. Immediately below that is a spot for me to enter
the point value. Both of these cells span two columns (i.e. column 6 and 7
for one question, 8 and 9 for the next, etc.). Then, underneath those cells
there are a total of 16 rows used for the first team, a set of separator rows
and then 16 more rows for the other team. Up to 8 quizzers can be on each
team but, because of how I have another macro setup, each quizzer has two
rows.

I've set up a macro that will move the selected cell from the current cell
(in a quizzer row...either column for a particular question) to the next
question's cell for the point value (row 7). However, before it moves to the
next question, it will check the current question to see if it was answered
(correct or incorrect). If the question was not answered at all, by either
team, it will gray the columns (and appropriate rows) associated with that
question.

Here's the wierd issue: If the macro does have to gray the column before it
moves to the next question, after I've entered the point value and pressed
enter, it will go to the next row down and the first of the two question
columns. If the macro does NOT have to gray the cells, when I press enter on
the next question, the cursor moves to the second column of the PREVIOUS
question.

I'll include the macro below. I hope I've delineated the situation and
problem sufficiently. Here's a for instance:
Question 1 uses Columns G and H. The point value for the question is
entered in row 7 (the cell listed is G7). I enter the score and then enter
the appropriate points(either correct or incorrect) for the appropriate
quizzer (their stats are on rows 8 through 23 and 28 through 43 for the
different teams). I click Ctrl-Shift-N to run the macro. It takes the
cursor to question two (cell I7). I enter the score and press enter and it
takes me to H8. If I had not entered any scores for the quizzer and the
macro had greyed those cells, it would correctly have taken my to I7 when I
pressed enter.

Here's the code:
Sub nextquestion()
'grays current question if there is a no response
'moves the cursor to the point value cell for the next question.
'hot key Ctrl+Shift+N

cl = ActiveCell.Column

'the first column in each question group is an odd numbered column.
this sets the column to odd

If cl Mod 2 = 0 Then cl = cl - 1

graycells = True

For x = 1 To 2

For y = 8 To 21
If (Cells(y, cl - 1 + x).Value <> "") And (Cells(y, cl - 1 +
x).Value <> " ") _ Then
graycells = False
Exit For
End If
Next y

If graycells = False Then Exit For

For y = 28 To 43
If (Cells(y, cl - 1 + x).Value <> "") And (Cells(y, cl - 1 +
x).Value <> " ") _ Then
graycells = False
Exit For
End If
Next y

If graycells = False Then Exit For

Next x

If graycells Then
Range(Cells(8, cl), Cells(21, cl + 1)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.5
.PatternTintAndShade = 0
End With
Range(Cells(28, cl), Cells(43, cl + 1)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.5
.PatternTintAndShade = 0
End With
End If


Range(Cells(7, cl + 2), Cells(7, cl + 2)).Select

End Sub

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top