Highlight errors of students work

hydros515

New Member
Joined
Jun 9, 2021
Messages
6
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Hello! I'm really in need of your help! Elementary school teacher here.
My teaching application outputs excel documentation that shows something like this. My answer (Correct Answer) is in Red and there are ONLY 3 parts to every question (So its 1,2,3 every question #). Is there a way in VBA I can highlight the students wrong answer in one go? Any help would be awesome! Thank you!
1624386235966.png
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,931
Office Version
  1. 2016
Platform
  1. Windows
Hi Hydros515,

Does it have to be VBA? This could be done using conditional formatting.

Could you paste cells A1 to E19 as text rather than an image to avoid retyping your test data?
 

hydros515

New Member
Joined
Jun 9, 2021
Messages
6
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Hi Hydros515,

Does it have to be VBA? This could be done using conditional formatting.

Could you paste cells A1 to E19 as text rather than an image to avoid retyping your test data?
Here you go!
Question #Question PartIDAnswer Doc 1Answer Doc 2
1​
1​
Teacherapplebird
1​
2​
Teacherbananacat
1​
3​
Teachercarrotdog
1​
1​
StudentAappleebird
1​
2​
StudentAbananacat
1​
3​
StudentAcarrottdog
1​
1​
StudentBapplebird
1​
2​
StudentBbanaacat
1​
3​
StudentBcarotdoggy
2​
1​
Teacher
10​
40​
2​
2​
Teacher
20​
50​
2​
3​
Teacher
30​
60​
2​
1​
StudentA
10​
41​
2​
2​
StudentA
21​
50​
2​
3​
StudentA
33​
60​
2​
1​
StudentB
10​
40​
2​
2​
StudentB
20​
50​
2​
3​
StudentB
31​
61​
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,931
Office Version
  1. 2016
Platform
  1. Windows
Does this do what you want?

Hydros515.xlsx
ABCDE
1Question #Question PartIDAnswer Doc 1Answer Doc 2
211Teacherapplebird
312Teacherbananacat
413Teachercarrotdog
511StudentAappleebird
612StudentAbananacat
713StudentAcarrottdog
811StudentBapplebird
912StudentBbanaacat
1013StudentBcarotdoggy
1121Teacher1040
1222Teacher2050
1323Teacher3060
1421StudentA1041
1522StudentA2150
1623StudentA3360
1721StudentB1040
1822StudentB2050
1923StudentB3161
20
21
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($E$2:$E$9999<>E2)),1))textNO
D2:D9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($D$2:$D$9999<>D2)),1))textNO
 

hydros515

New Member
Joined
Jun 9, 2021
Messages
6
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

Does this do what you want?

Hydros515.xlsx
ABCDE
1Question #Question PartIDAnswer Doc 1Answer Doc 2
211Teacherapplebird
312Teacherbananacat
413Teachercarrotdog
511StudentAappleebird
612StudentAbananacat
713StudentAcarrottdog
811StudentBapplebird
912StudentBbanaacat
1013StudentBcarotdoggy
1121Teacher1040
1222Teacher2050
1323Teacher3060
1421StudentA1041
1522StudentA2150
1623StudentA3360
1721StudentB1040
1822StudentB2050
1923StudentB3161
20
21
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($E$2:$E$9999<>E2)),1))textNO
D2:D9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($D$2:$D$9999<>D2)),1))textNO
Yes!
 

hydros515

New Member
Joined
Jun 9, 2021
Messages
6
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Does this do what you want?

Hydros515.xlsx
ABCDE
1Question #Question PartIDAnswer Doc 1Answer Doc 2
211Teacherapplebird
312Teacherbananacat
413Teachercarrotdog
511StudentAappleebird
612StudentAbananacat
713StudentAcarrottdog
811StudentBapplebird
912StudentBbanaacat
1013StudentBcarotdoggy
1121Teacher1040
1222Teacher2050
1323Teacher3060
1421StudentA1041
1522StudentA2150
1623StudentA3360
1721StudentB1040
1822StudentB2050
1923StudentB3161
20
21
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($E$2:$E$9999<>E2)),1))textNO
D2:D9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($D$2:$D$9999<>D2)),1))textNO
Actually can you help we with the steps on this?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,931
Office Version
  1. 2016
Platform
  1. Windows
Actually can you help we with the steps on this?
You mean explain the formulae? Sure!

The IF statement says ignore this row if the ID is "Teacher" or null (as we don't want to highlight Teacher or empty rows).

The AGGREGATE(15,6 is a multipurpose function but the 15 option invokes the SMALL function, 6 means ignore errors within the function and the ",1)" at the end says we're looking for the SMALLest but in fact we only want the first.

The "ROW($A$2:$A$9999)-ROW($A$1)" says we're not looking for the SMALLest number but we're going through rows 2 to 9999 minus 1. This is then divided by the rest of the formula. If the rest of the formula is TRUE (logical 1) then whatever row number we're at is divided by the logical 1 and we get the row number returned, which is treated as a TRUE by the Conditional Format and it gets highlit.
The comparisons to the right of the divide sign are multiplied together so only if all are logical 1 will a logical 1 be returned. If any are logical 0 then that generates a zero, which when divided into the current row of AGGREGATE generates a #DIV/0! error, but our "6" option ignores it.

Let's look at an example of cell D7.
"carrott" isn't "Teacher" or null so the IF falls into the AGGREGATE.
The comparison logic in the Conditional Format will be (($A$2:$A$9999=A7)*($B$2:$B$9999=B7)*($C$2:$C$9999="Teacher")*($D$2:$D$9999<>D7)) so it's checking for column A=1, column B=3, column C="Teacher" and column D<> (not equal) to "carrott" so it find row 4 with 1, 3, "Teacher" and "carrot" is indeed not equal to "carrott" so all are true, so 1*1*1*1=1 which divided into the row we're looking at returns a 3. Conditional Formatting treats that number as a TRUE and "carrott" is highlit.
If no rows satisfy the checks then AGGREGATE returns a #NUM error which is treated by Conditional Formatting as a FALSE so it is not highlit.

The "Answer Doc 2" column E is the same thing with slight column reference changes.

You can check the Conditional Format formulae by actually entering them into some columns. You'll se a FALSE if it satisfies the IF statement, a #NUM if the student answer is correct (i.e. the Teacher answer is equal to the student answer) or the row number of the Teacher answer if it did not match the student answer.

Hydros515.xlsx
ABCDEFGH
1Question #Question PartIDAnswer Doc 1Answer Doc 2
211TeacherapplebirdFALSEFALSE
312TeacherbananacatFALSEFALSE
413TeachercarrotdogFALSEFALSE
511StudentAappleebird1#NUM!
612StudentAbananacat#NUM!#NUM!
713StudentAcarrottdog3#NUM!
811StudentBapplebird#NUM!#NUM!
912StudentBbanaacat2#NUM!
1013StudentBcarotdoggy33
1121Teacher1040FALSEFALSE
1222Teacher2050FALSEFALSE
1323Teacher3060FALSEFALSE
1421StudentA1041#NUM!10
1522StudentA215011#NUM!
1623StudentA336012#NUM!
1721StudentB1040#NUM!#NUM!
1822StudentB2050#NUM!#NUM!
1923StudentB31611212
20FALSEFALSE
21FALSEFALSE
Sheet1
Cell Formulas
RangeFormula
G2:G21G2=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($D$2:$D$9999<>D2)),1))
H2:H21H2=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($E$2:$E$9999<>E2)),1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($E$2:$E$9999<>E2)),1))textNO
D2:D9999Expression=IF(OR(C2="Teacher",C2=""),FALSE,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2)*($C$2:$C$9999="Teacher")*($D$2:$D$9999<>D2)),1))textNO
 

Forum statistics

Threads
1,141,862
Messages
5,709,045
Members
421,609
Latest member
misskittens

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top