Conditional Formatting using Event Macros
 

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

Conditional Formatting using Event Macros
 

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

Conditional Formatting using Event Macros
 

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

Conditional Formatting using Event Macros
 

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

Conditional Formatting using Event Macros
 

Try it like this. You IF and Endif statements are not constructed as you
think they a



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 = Truer

--
Regards,
Tom Ogilvy

"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

Conditional Formatting using Event Macros
 

You need to post your change event code.

Think you selected the selectionchange event.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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

Conditional Formatting using Event Macros
 

LOL. You are right, the OP didn't include the event. Had to get one to make
it run, then forgot I had.

Bob


"Tom Ogilvy" wrote in message
...
You need to post your change event code.

Think you selected the selectionchange event.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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