I'm building a simple risk log and would appreciate some help.

Users select one of 5 probability values (rare, unlikely, ....almost
certain) in col N
Users select one of 5 impact value (insignificant, minor,
major...catastrophic!) in Col N+1. Each of these is restricted to selecting
from a simple validation list.

The value in Column N+2 (the risk rating) is then generated from the
specified combination of probability and impact and referring to 5X5 matrix
(I use the MATCH and INDEX functions to achieve this. No problem so far,
that works and generates a Risk value from LOW through to EXTREME.

I then try and use the code below to generate a fill/background color to
match the risk rating in the cell (getting around the conditional formatting
limit of only 3 possible values in Excel 2003)

This code works fine if I simply type in the Risk Rating value but not if I
generate it as above. It's as if the cell is not registering the change of
value. Lack of focus??

Any assistance most appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("L16:L56")) Is Nothing Then
Select Case Target
Case "Low"
icolor = 3
Case "Moderate"
icolor = 46
Case "High"
icolor = 6
Case "Very High"
icolor = 43
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If
End Sub