This small change got it running for me, but the code doesn't; fully agree
with your description. Would it not be more appropriate to use the change
event as well?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 6 Then Exit Sub
If Target.Row <= 10 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column = 6 Then
Importance = Target.Offset(0, -3).Value
Weight = Target.Offset(0, -1).Value
Score = Target.Offset(0, -4).Value

If Score = 0 Then GoTo ErrHandler
If Score = "" Then GoTo ErrHandler

' if completed -- score is blue
If Weight = 5 Then
Target.Offset(0, -4).Interior.ColorIndex = 5
' if incomplete, but low it is yellow, else red
ElseIf Weight = 1 And Importance = 1 Then
Target.Offset(0, -4).Interior.ColorIndex = 6
ElseIf Weight = 1 And Importance < 1 Then
Target.Offset(0, -4).Interior.ColorIndex = 3
' otherwise if score 6 its green, else yellow
ElseIf Score 6 Then
Target.Offset(0, -4).Interior.ColorIndex = 4
Else
Target.Offset(0, -4).Interior.ColorIndex = 6
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PamKT" wrote in message
...
Thanks (I'm new to this)


If Target.Column < 6 Then Exit Sub
If Target.Row <= 10 Then Exit Sub




On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column = 6 Then
Importance = Target.Offset(0, -3).Value
Weight = Target.Offset(0, -1).Value
Score = Target.Offset(0, -4).Value


If Score = 0 Then GoTo ErrHandler
If Score = "" Then GoTo ErrHandler

' if completed -- score is blue
If Weight = 5 Then Target.Offset(0, -4).Interior.ColorIndex = 5


' if incomplete, but low it is yellow, else red
ElseIf Weight = 1 And Importance = 1 Then Target.Offset(0,
-4).Interior.ColorIndex = 6
ElseIf Weight = 1 And Importance < 1 Then Target.Offset(0,
-4).Interior.ColorIndex = 3

' otherwise if score 6 its green, else yellow
ElseIf Score 6 Then Target.Offset(0, -4).Interior.ColorIndex = 4
Else: Target.Offset(0, -4).Interior.ColorIndex = 6
End If
ErrHandler:
Application.EnableEvents = True


"Tom Ogilvy" wrote:

You need to post your change event code.

--
Regards,
Tom Ogilvy


"PamKT" wrote in message
...
I have a spreadsheet that collects responses to questions. Each
question
is
weighted with a H, M, L rate (col d) of importance (3,2,1
respectively)
(col
c). The user will select the response to a question using a dropdown
(validation list) unique to each question (col f). Each response has a
numeric equivalent (col e). My goal is to have the score
(weight*response)
be
posted in column B and the cell interior changed to Blue, Green,
Yellow,
Red,
or Neutral based on some additional criteria.

I've read through all the previous postings and recommended web links.
I
understand that I will have to use one of the event macros to trigger
the
application code. I've followed the basic design from
http://www.mvps.org/dmcritchie/excel/event.htm#change

But -- it does not work consistently. Its almost as though I get into
a
situation where the trigger event is being ignored. I've put
breakpoints
into the macros, and I don't reach them. If I open the worksheet, it
works
for the first few that I change. But I don't seem to be able to get
the
macro
to trigger every time I select a choice from the cell dropdown. I'm
not
sure
if I should be using Worksheet_calculate, Worksheet_Change or
Worksheet_SelectionChange. I understand the latter 2 have a relation
to
entering or exiting a cell.


Any help would be appreicated. I've spent way more time on this than
I
should have, so I'm reaching out for help.

Thanks