Conditional formatting for grading multiple choice exam

[mandm1516]

I'm teaching a class where I thought I'd test giving the final exam online. The online version gives me a summary of correct/incorrect answers (the test is multiple choice) but to correct individual tests I have to export it to excel. I'm trying to use conditional formating to grade each test.

Column A is each students name and moving to the left is the answer to each question (i.e. column B is answer 1, column B is answer 2). There are 58 questions/answers.

The top row is the key with correct answers. I've managed to use conditional formating to color all the anwers in Column B (first question) if they match the answer at the top of the column so cells that aren't colored are incorrect. Now if I could copy this conditional formating to the rest of the spread sheet I'd be in great shape.

I've tried using the Format Painter to copy the first answer column/conditional formating across the spread sheet but it keeps comparing all answers to the first question key rather than the correct answer for each subsequent answer (i.e. the answer to #1 is 2--it colors all the #2s in the formula despite the correct answer for question 3 being 4).

Is there a way to copy the conditional format for each row, rather than setting up a new conditional format for each row separately?

Thanks.

Mike

[ChemistB]

Select your original range (column B2:B100 or whatever) and go to your conditional formatting > Manage rules> Edit
See how you referenced your cell. It's probably $B$1. Change that to B$1 and "OK" your way out. Then try copying the format to the other columns. Did that work for you?

[mandm1516]

ChemistB, looks like that did the trick. I was deleting both $ (from $C$4 to C4), removing just the first one made all the difference. Thanks!!

Mike