Button Press for Condtional Formatting Based on Cell Values

[Caelus]

Good day,

I'm using Excel 2010 and creating what is essentially a study tool - a comparison between two lists (one that the user populates and the one beside it which has the answers). There are three columns in each list but there are merged cells that serve as headers for each sub-section.

I have figured out macro recording, button creation, and assigning macros to buttons. I have also noticed that I can record macros involving conditional formatting. What I cannot figure out how to do is this:

I would like the user to be able to enter a bunch of data in the first field (three columns) and then click a button to "validate" the entered data. If the data matches the field that contains the answers, I would like the user's data boxes to turn green in each correct instance. I do not want the boxes to turn green until the button press. I would also like the option of reverting the cells back to normal (clear the green fill).

In a more advanced version, I would like yellow boxes for user-entered data which is correct in lettering but perhaps not exact in formatting (capitals or lower case).

 

[Snakehips]

Caelus,

Welcome to MrExcel.

Are you looking to have just one button that toggles validation/ CF'ing for the the whole range of answers ?

 

[Caelus]

Yes, but I'm open to other solutions. Ideally the user would simply enter all their data and just hit one button to reveal all those cells which are "correct" in relation to the field with the answers.

 

[Snakehips]

Here is a suggestion that will not require vba.

Instead of a button you can insert a checkbox, (or an option button).
Right click the checkbox >> Format Control >> Control tab and set the linked cell.

In the below example I1 is the linked cell.
The value of this cell will toggle between TRUE and FALSE with the checkbox.

Then apply standard conditional formatting similar to the below to check the student answers against the required answers and the state of I1.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Student Answers				Correct Answers			Validation	TRUE
2	Section 1
3	ANSWER1	b1	C1		Answer1	B1	C1
4	A2	B2	C5		A2	B2	C2
5	A3	B3	Rubbish		A3	B3	C3
6	A6	b6	c4		A4	B4	C4
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	1. / Formula is =AND($I$1=TRUE,EXACT(A3,E3))	Abc
A3	2. / Formula is =AND($I$1=TRUE,A3=E3)	Abc

Hope that helps.

 

[Caelus]

Wonderful. It looks good - I'll get back to you soon!

 

[Caelus]

Ok, I'm back. Sorry for the delay.

Your suggested solution worked perfectly when I figured out that you had ordered the rules that way for a reason! Thanks for the quick and effective reply!