Conditional Formatting

[Tazzy via OfficeKB.com]

I am putting together a worksheet where students have to enter formula to
calculate a times table, at the moment it looks like this;

1 2 3 4 5
1
2
3
4
5
going all the way to 10 in columns and rows. To calculate the correct number,
they have to use a variety of mixed cell references. I want to be able to
enter conditional formatting to make each cell change to a different colour
if they use the exact formula, eg, = $A4*B$3, etc. So far I'm not having a
lot of success as the conditional formatting does not seem to take any
account of whether the $ sign is present or not. Can anyone please help,
thanks.

 

[Guest]

Hi Tazzy,
excel reads/calculates the cell refs factors for a certain result - with or
without a $.
Commonly used for productive formulation like the times table you mentioned.
If you need to trace the input formula to have a - must $ - then you may
need to view it, as a teacher if you are, Tools -view -formulas.
Correction / checking of student work may need direct and visual check on
its input.

happy hoilidays...

 

[Tazzy via OfficeKB.com]

Hi Driller,

Thanks for your answer, and I agree, I will need to verify the exact formula
they have put in. It is to be used as a homework exercise and I was going to
lock and protect the sheet, apart from the blank cells where they have to
enter the frmula. I was hoping that they would have to work at this, knowing
that when they got the formula exact, the cell would change colour, and so
they would then realise that they had got the formula correct. Any
suggestions?

 

[Guest]

Hi Tazzy,

You could try a macro on the worksheet. I checks if the answer is not
correct, it is just a number is 24 for 8X3 or otherwise.

You need to consider how you want to check the formulas but you just need
to parse the szFormula string and consider whay you want to do.

'============start==========
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim lCol As Long
Dim lAns As Long
Dim rTable As Range
Dim szFormula As String
Application.EnableEvents = False
Set rTable = ActiveSheet.Range("B2:K11")
If Not Intersect(rTable, Target) Is Nothing Then
szFormula = Target.Formula
lRow = Target.Row
lCol = Target.Column
lAns = (lRow - 1) * (lCol - 1)
If Target.Value <> lAns Then
Target.Interior.ColorIndex = 3 ' incorrect ans
ElseIf szFormula = Target.Value Then
Target.Interior.ColorIndex = 6 ' just a number
Else
Target.Interior.ColorIndex = 8 ' formula
End If
End If
Application.EnableEvents = True
End Sub
'============end===========
This macro should be placed in the code for the specific worksheet.