Teacher Grading sheet with variable formatting

[linkedconsultant]

Hopefully there is an easy solution that is alluding me.
What I have is a sheet that has different questions represented by each column, students and their answers in each row.

I need to compare the answer for each student with the correct answer at the top of the column. If the answer is wrong the cell should fill with RED. If the answer is correct AND the row number is even, then the fill should be WHITE/No Fill, else if the row number is odd then the fill should be GREY.

The number of questions (therefore the number of columns) will vary by teacher and so will the number of students (therefore the number of rows).

Ideally the macro would be smart enough to find a couple of anchor points to figure out the range, but I can certainly live with having someone select the range and then execute the macro.

Like I say, I hope the solution is simple but I'm having trouble keeping the variables straight. Thanks.

 

[VoG]

Hello and welcome to MrExcel.

Try Conditional Formatting:

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >Correct:</td><td >A</td><td >B</td><td >C</td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >Student 1</td><td style="background-color:#a5a5a5; ">A</td><td style="background-color:#ff0000; ">A</td><td style="background-color:#ff0000; ">A</td><td style="background-color:#a5a5a5; ">A</td><td style="background-color:#ff0000; ">A</td><td style="background-color:#ff0000; ">A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >Student 2</td><td style="background-color:#ff0000; ">B</td><td >B</td><td style="background-color:#ff0000; ">B</td><td >A</td><td >B</td><td style="background-color:#ff0000; ">B</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >Student 3</td><td style="background-color:#ff0000; ">C</td><td style="background-color:#ff0000; ">C</td><td style="background-color:#a5a5a5; ">C</td><td style="background-color:#ff0000; ">B</td><td style="background-color:#a5a5a5; ">B</td><td style="background-color:#a5a5a5; ">C</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B3</td><td >1. / Formula is =AND(B3=B$1,MOD(ROW(),2)=1)</td><td style="background-color:#a5a5a5; ">Abc</td></tr><tr><td >B3</td><td >2. / Formula is =AND(B3=B$1,MOD(ROW(),2)=0)</td><td style="">Abc</td></tr><tr><td >B3</td><td >3. / Formula is =B3<>B$1</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[linkedconsultant]

I appreciate the response, that is what I was going to do but I was hoping to have a Macro do the work rather than having to set the conditional formatting. May end up there, we will see!