Here is another approach.
In each of the Severity and Likelihood cells, there is a Dropdown Content Control containing the numbers from 1 to 5 (1 being the lowest risk and 5 the greatest) from which the user selects the appropriate value. Then in the fourth column and the sixth
column, there is a field that computes the product of the values selected in the two cells to the left.
The calculation does not however occur automatically, rather it is performed by the running of a macro that contains the following code, which in addition to performing that calculation, also applies the relevant colour to each of the cells in the second
through seventh columns of the table.
This is the code in the macro, which is run from a button on a custom tab on the ribbon
Dim i As Long, j As Long, k As Long, t As Long
Dim acell As Cell
Dim crange As Range
Dim timestart As Date
Dim timeend As Date
Dim elapsedtime As Long
Sub ColorizeAll()
timestart = Now
On Error GoTo ExitThis
With ActiveDocument
For t = 2 To .Tables.Count Step 2
With .Tables(t)
For i = 1 To .Rows.Count - 1
If Len(.Cell(i, 1).Range.Text) > 2 Then
j = i
Else
Exit For
End If
Next i
For i = 1 To j
With .Rows(i)
.Cells(4).Range.Fields.Update
.Cells(7).Range.Fields.Update
For k = 2 To 7
Set crange = .Cells(k).Range
crange.End = crange.End - 1
Select Case Val(crange.Text)
Case 1 To 4
crange.Cells(1).Shading.BackgroundPatternColor = RGB(153, 204, 0)
Case 5 To 12
crange.Cells(1).Shading.BackgroundPatternColor = RGB(255, 153, 0)
Case Is > 12
crange.Cells(1).Shading.BackgroundPatternColor = RGB(255, 80, 80)
Case Else
If Len(crange.Text) < 5 Then
crange.Cells(1).Shading.BackgroundPatternColor = RGB(255, 255, 255)
End If
End Select
Next k
End With
Next i
End With
Next t
End With
This system is used in a document that contains Risk Assessments for something like 100 activities and situations. If you only had a couple of Risk Assessments to deal with, it would be feasible for the calculation and coloring of the cells to be performed
by the use of the Document Content Control On Exit event.
