change cell colors after comparing against an answer row

[cpliu]

I have users' answers in multiple rows. my top row has the right
answer keys.
Each column represents answer to a question.

How can I compare the all the rows against the right answer keys and
give a highlights on all the wrong answers?

A1 to A10 is the right answers for question 1 to question 10:
B,D,D,D,A ,B,A,A,B

B1 to B10 is the answers from one user. In the example blow, I'd like
to compare 2 rows and highlight the B2 cell in yellow (for it's the
wrong answer)
B,C,D,D,A ,B,A,A,B

.... continue with the rest of users.

How can I do that?

Thanks,

 

[Bob Umlas]

Select B1:Z10 (or through the last user)
Use Conditional formatting & in xl 2003 change Value is to Formula Is; in
2007 click on the Use a formula...
Assuming B1 is the active cell, enter:
=B1<>$A1
then click the Format button and select the yellow fill format
(Use of the "$" is important in the formula)
Bob Umlas
Excel MVP

 

[liu]

Select B1:Z10 (or through the last user)
Use Conditional formatting & in xl 2003 change Value is to Formula Is; in
2007 click on the Use a formula...
Assuming B1 is the active cell, enter:
=B1<>$A1
then click the Format button and select the yellow fill format
(Use of the "$" is important in the formula)
Bob Umlas
Excel MVP

Thanks for the help. It works great.

 

[liu]

To expand from this how can I calculate the total correct answers and
put it at the end? countIf? but don't know how to compare 2 cells
inside it.

Thanks again for the help,

 

[Shane Devenshire]

Hi,

Not sure where you want to put this but suppose you want to count the number
of correct answers in B1:Z1, where the correct answer is in A1:

=COUNTIF(B1:Z1,A1)

You could enter this in AA1 and copy it down.

 

[liu]

For example my data like one below.
right ans: B,D,D,D,A,B,A,A,B,D
student 1: B,D,D,D,D,B,A,A,B,A
student 2: B,D,D,D,A,B,A,A,B,C
student 3: ...
....

In the case above, I'd like to count the scores for each row
(representing a student).
student 1 would be 80, student would 90 in the case above.

If not possible, can you count how many cells from E3 to N3 that is of
no fill color? Then that's score too.

Thanks for the help,