Conditional Format cell when inputting correct math answer

[stevembe]

Firstly thank you for taking the time to read. I am tryingto put together a spreadsheet that has simple maths questions, subtraction andaddition. So if I have in cell A1 the number 2 and in cell B1 the number 3 Iwant cell C1 to turn green when the number 5 is entered and similarly if theincorrect answer is put in it turns red. Same again if A1 has 5 and B1 -3 cellC1 goes green when 2 is entered correctly and again red for a wrong answer. Gratefulif anyone could advise on a way of doing this.

 

[Joe4]

You will have two Rules, both using the "Formula" option.

Use this Conditional Formatting formula in C1 for green: =A1+B1
and then use this Conditional Formatting formula in C1 for red: <>A1+B1

 

[stevembe]

You will have two Rules, both using the "Formula" option.

Use this Conditional Formatting formula in C1 for green: =A1+B1
and then use this Conditional Formatting formula in C1 for red: <>A1+B1

Thanks Joe, tried that but the cells remain green all the time regardless of what is entered into C1

 

[Joe4]

Did you delete all old attempts first, so there are no residual rules out there?
It is pretty straightforward, and should work.

If you cannot get it to work, do the following. Recreate it on a new file, following these steps.
1. Open a new Excel file
2. Turn on the Macro Recorder (View -> Macros -> Record New Macro)
3. Enter your two Conditional Formatting rules for cell C1
4. Enter data in cells A1 and B1 that should turn it red
5. Stop the Macro Recorder
6. Go to the Macros Menu, select "View Macros", select the Macro you just recorded, and click the "Edit" button
7. Copy and Paste the VBA code displayed here.

This will allow me to see exactly how you have done it all, and I can run it here to set up your exact scenario, and see where you might be running into problems.

 

[BlueHornet]

I would add a third condition to that: = ISBLANK( C1) with no formatting (or a border-outlined box, perhaps), make it the first condition and check "Stop if true" so the inequality condition is not triggered. That will avoid having a sea of red boxes to fill in, which might be intimidating to the test-takers.

Eventually, of course, you'll want to limit the cell entries to "number only", so that smart-aleks (such as me) don't just fill in the formula that you're looking for an answer to, which will, of course, produce the right answer. Baby steps, though.

 

[stevembe]

Joe, this is what I have:

Sub Macro1()
'
' Macro1 Macro
'
'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A1+B1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""<>A1+B1"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("C3").Select
End Sub

 

[Joe4]

My bad, I forgot we need to tweak that formula a bit when using the formula option.
If you are putting the answer in cell I4, the formulas should be:
=I4=A1+B1
and
=I4<>A1+B1