if statement with conditional formatting or other solutions

Hello Dear Excel Experts!!

I have a particular situation, I am taking my exam and practicing some questions from a question bank. I would like to create a spread sheet which I could use to track answers to the questions that I solve. I have the correct answer choices to the questions that i am going to solve, but I would like to create two columns which would named "my answer choices" (suppose column B) and "Correct Answer choices" (suppose column C).

lets suppose if the correct answer choice of question # 2 is D: if I enter D in column B, the cell should be formatted with green fill, and if I enter any other answer choice (A,B,C OR E), it should be formatted with red fill, at the same time, it should also show correct answer in column C after I enter my choice, otherwise column C should be blank. Just to make sure that I don't cheat ;-)

I would appreciate if someone can help me figure this out!!

thanks in advance!

-Vijay

Are the correct answers located somewhere else within the spreadsheet? If so, can you upload an example so we can formulate it appropriately.

Without having the correct references and if you want to try to formulate it yourself, do this.

In column C, you want to only show if B has something in it. so...

Formula:

Please Login or Register  to view this content.

To handle the conditional formatting, add 2 conditional formatting

Formula:

Please Login or Register  to view this content.

You can formulate ANSWER by using an INDEX or LOOKUP

Thank you for your reply Quekbc, I attached the excel file with the correct answer choices, it would be helpful if you can illustrate in that.

Appreciate your help!

-Vijaydoli

This should do it.

Thank you, this is perfect!!

Not a problem Vijay. Good luck with your exams.