Hello MrExcel community,
I'm working with a database comprised of answers to a test given by students and am trying to conditionally format them such that correct answers are highlighted green and incorrect and blank entries are highlighted red. The database is structured with columns for each question and rows corresponding to each individual students answer. The actual correct answers are indicated below each corresponding column. It is important to note that each column may have two correct answers.
I have successfully created formatting rules for correct answers and blank entries, however, my attempts for formatting incorrect entries have failed so far. When applying a rule where only one of the correct answers is used as the condition, the incorrect cells are highlighted as intended (below). However, when I try to create a condition checking for either correct answer A or B, the entire selection of cells turns red.
Below is an excerpt from the database (highlighted cells = student answers, final 2 cells = correct answers).
Currently I am using this formatting rule (where S32 and S33 are the correct answers):
=S3:S29<>S$32
I applied the following rule to include both correct answers:
=S3:S29<>(OR(S$32,S$33))
This did not work. I also tried to create two separate rules in the first style but to no avail. Moreover, using a NOT-OR statement did not work either.
Could someone please provide a solution and explain whether I've conceptualised the problem wrong? Any help is much appreciated!
I'm working with a database comprised of answers to a test given by students and am trying to conditionally format them such that correct answers are highlighted green and incorrect and blank entries are highlighted red. The database is structured with columns for each question and rows corresponding to each individual students answer. The actual correct answers are indicated below each corresponding column. It is important to note that each column may have two correct answers.
I have successfully created formatting rules for correct answers and blank entries, however, my attempts for formatting incorrect entries have failed so far. When applying a rule where only one of the correct answers is used as the condition, the incorrect cells are highlighted as intended (below). However, when I try to create a condition checking for either correct answer A or B, the entire selection of cells turns red.
Below is an excerpt from the database (highlighted cells = student answers, final 2 cells = correct answers).
Currently I am using this formatting rule (where S32 and S33 are the correct answers):
=S3:S29<>S$32
I applied the following rule to include both correct answers:
=S3:S29<>(OR(S$32,S$33))
This did not work. I also tried to create two separate rules in the first style but to no avail. Moreover, using a NOT-OR statement did not work either.
Could someone please provide a solution and explain whether I've conceptualised the problem wrong? Any help is much appreciated!
