VBA to Test Validity of Answers

[njmiller31]

Hello,

I have created a multiplication test for 2nd grader. The goal is for him to fill out the table on the "Test" sheet and then run my macro. The macro would then compare his answers to the those on the (locked) "Answers" sheet. The macro would then display a MsgBox with the number of correct answers as well as highlight the answers he got wrong on the "Test" sheet.

I've been (surprisingly) successful with everything up until it comes time to compare and highlight the incorrect answers. Here's what I have for that part:

[FONT=arial]Sub Correct_Incorrect()[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Dim correct_answers As Range[/FONT]
[FONT=arial]Dim test_answers As Range[/FONT]
[FONT=arial]Dim cell As Range[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]Set correct_answers = Worksheets("Answers").Range("<wbr>B2:M13")[/FONT]
[FONT=arial]Set test_answers = Worksheets("Test").Range("B2:<wbr>M13")[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]For Each cell In test_answers[/FONT]
[FONT=arial]    If cell.Value = correct_answers.Value Then[/FONT]
[FONT=arial]        Cells.Interior.ColorIndex = 50[/FONT]
[FONT=arial]    Else[/FONT]
[FONT=arial]        Cells.Interior.ColorIndex = 3[/FONT]
[FONT=arial]    End If[/FONT]

[FONT=arial]Next cell[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]
[/FONT]
[FONT=arial]End Sub[/FONT]

Any ideas where I'm going wrong

 

[Akuini]

You have to compare the cells one by one. Try this:
I assumed there are no blank cells in range Worksheets("Answers").Range("B2:M13").

Sub Correct_Incorrect()
Dim i As Long
Dim j As Long
Dim k As Long

With Worksheets("Test")
For i = 2 To 13
  For j = 2 To 13
    If .Cells(i, j) = Worksheets("Answers").Cells(i, j) Then
    .Cells(i, j).Interior.ColorIndex = 50
    k = k + 1
    Else
    .Cells(i, j).Interior.ColorIndex = 3
    End If
  Next j
Next i
End With

 MsgBox k & " correct answer"
End Sub

 

[mikerickson]

When matching two ranges, the For Each loop isn't a good choice.

Dim correct_answers As Range
Dim test_answers As Range
Dim i As Long, j as Long

Set correct_answers = Worksheets("Answers").Range("B2:M13")
Set test_answers = Worksheets("Test").Range("B2:M13")


For i = 1 to 12
    For j = 1 to 12
        If test_answers.Cells(i,j).Value = correct_answers.Cells(i, j).Value Then
             test_answers.Cells(i,j).Interior.ColorIndex = 50
        Else
            test_answers.Cells(i,j).Interior.ColorIndex = 3
        End If
    Next j
Next i

 

[njmiller31]

This worked perfectly. Thank you!