Teacher Grading sheet with variable formatting

linkedconsultant

New Member
Joined
Aug 10, 2009
Messages
4
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.

 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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-style:eek:utset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;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-style:eek:utset;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-style:eek:utset;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-style:eek:utset;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-style:eek:utset;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

New Member
Joined
Aug 10, 2009
Messages
4
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!
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top