CODEWITHSUNDEEP
excel
Hanief Han
Hanief Han

Reputation: 160

Change color based on value from another table

I have an answer sheet (in sheet tab "Answer Sheet") in Excel like this:

Answer Sheet

And I have this table for correct answers (in sheet tab "Correct Answer") like this:

Correct Answer

I want to change the color for incorrect answers with red background.

And I want to create a "toggle" to show the incorrect answers (if I change the value to "TRUE"), maybe like this:

Incorrect Toggle

How do to it?

Upvotes: 1

Views: 972

Answers (1)

the_hook86
the_hook86

Reputation: 61

This can be done with the conditional formatting manager: Start => Conditional Formatting => New Rule => Based on Formula (last option) In the Formula you will click on the answer sheet in B2 and remove the "$" type "<>" (which stands for not equal) and then select B2 in the correct answer sheet:

= B2<>CorrectAnswer!B2

Then you will then change the format to the style you like and click ok.

Edit: to extend the area the formatting applies to, you can copy the formatting by using the formatting paster: https://www.solveyourtech.com/copy-cell-formatting-from-one-cell-to-another-in-excel-2010/ or by extending the area it applies to after you defined the rule: https://www.excel-easy.com/examples/images/manage-rules/conditional-formatting-rules-manager.png

Toggling: This can be done in the manager by setting it to active or inactive. https://excelunplugged.files.wordpress.com/2014/06/060314_0545_turnconditi6.png Alternatively: you could write the formula like this:

= and(B2<>CorrectAnswer!B2;$G$9=TRUE)

Upvotes: 1

Related Questions