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

You are in focus but at the wrong event!

Because the cells containing Risk Rating contain formulae, the Change
worksheet event won't respond to them.

Use the worksheet Calculate event instead.
--
Gary''s Student - gsnu200815


"Jono" wrote:

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

Thanks G'S. That makes sense!!

However....if you'll excuse this newbie...
How do I then control which Column the Calculate event looks at.

For example, if I enter values in, say, Cols C and D and the overall rating
is generated by the formulae in Col E. How do I target the Calculate event
to change the colour of the changed cell in Col E.

thanks again.
J.


"Gary''s Student" wrote:

You are in focus but at the wrong event!

Because the cells containing Risk Rating contain formulae, the Change
worksheet event won't respond to them.

Use the worksheet Calculate event instead.
--
Gary''s Student - gsnu200815



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

You don't need to. Remember the Calculate event doesn't have a Target. It
does not care which cells change. Just refresh the colour of ALL the
potential cells in column E
--
Gary''s Student - gsnu200815


"Jono" wrote:

Thanks G'S. That makes sense!!

However....if you'll excuse this newbie...
How do I then control which Column the Calculate event looks at.

For example, if I enter values in, say, Cols C and D and the overall rating
is generated by the formulae in Col E. How do I target the Calculate event
to change the colour of the changed cell in Col E.

thanks again.
J.


"Gary''s Student" wrote:

You are in focus but at the wrong event!

Because the cells containing Risk Rating contain formulae, the Change
worksheet event won't respond to them.

Use the worksheet Calculate event instead.
--
Gary''s Student - gsnu200815



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