Conditional Formatting using Event Macros

PamKT · 10-23-2005, 11:05 AM

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

Tom Ogilvy · 10-23-2005, 11:05 AM

You need to post your change event code.

--
Regards,
Tom Ogilvy

"PamKT" <[email protected]> wrote in message
news:[email protected]...
> 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

PamKT · 10-23-2005, 01:05 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Bob Phillips · 10-23-2005, 01:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

Tom Ogilvy · 10-23-2005, 01:05 PM

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

Tom Ogilvy · 10-23-2005, 02:05 PM

> You need to post your change event code.

Think you selected the selectionchange event.

--
Regards,
Tom Ogilvy

"Bob Phillips" <[email protected]> wrote in message
news:OBSDW$%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>

Bob Phillips · 10-23-2005, 04:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> > You need to post your change event code.
>
> Think you selected the selectionchange event.
>
> --
> Regards,
> Tom Ogilvy
>
> "Bob Phillips" <[email protected]> wrote in message
> news:OBSDW$%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
>
>

PamKT · 10-23-2005, 07:05 PM

Here's what I really did.. I'm not sure about the "= Truer" -- was that
supposed to be "= True" (last line in the recommendation)

What happened was that nothing worked, No update was made to Column B
related to color

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub Worksheet_Calculate()

Call SetColors
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Call SetColors

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call SetColors

End Sub

And then my SetColors did this (based on your suggestion)
Sub SetColors()

' only interested in changes in the RESPONSE column (5)
' when it changes, we want to see what the other values are
' Importance and Weight
Dim Target As Range
Worksheets("Sheet1").Activate
Set Target = ActiveCell

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

End Sub

"Tom Ogilvy" wrote:

> Try it like this. You IF and Endif statements are not constructed as you
> think they are:
>
>
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

Tom Ogilvy · 10-23-2005, 08:05 PM

the r on the end of true must be some residual typing - as far as I know, I
didn't touch that line.

Target holds a reference to the cell which triggered the macro. Changing it
to ActiveCell is probably problematic.

If you want to react to changes in column 5, then you should check for
Target.Column = 5

Not sure why you are activating a sheet,
Worksheets("Sheet1").Activate
You should already be on the sheet you want to work with.

Why not get your basic If then else code working in a separate macro based
on a known cell, then adapt to your change event.

Either that or use absolute cell references

for example, rather than using offset as you do here

> Importance = Target.Offset(0, -3).Value
> Weight = Target.Offset(0, -1).Value
> Score = Target.Offset(0, -4).Value

use

Importance = Cells(Target.row,"A").value
Weight = Cells(Target.row,"B").Value
Score = cells(Target.Row,"C").Value

use the correct column letter of course. Continue that approach in all your
code.

--
Regards,
Tom Ogilvy

"PamKT" <[email protected]> wrote in message
news:[email protected]...
> Here's what I really did.. I'm not sure about the "= Truer" -- was that
> supposed to be "= True" (last line in the recommendation)
>
> What happened was that nothing worked, No update was made to Column B
> related to color
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>
>
>
> Private Sub Worksheet_Calculate()
>
> Call SetColors
> End Sub
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Call SetColors
>
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Call SetColors
>
> End Sub
>
>
> And then my SetColors did this (based on your suggestion)
> Sub SetColors()
>
> ' only interested in changes in the RESPONSE column (5)
> ' when it changes, we want to see what the other values are
> ' Importance and Weight
> Dim Target As Range
> Worksheets("Sheet1").Activate
> Set Target = ActiveCell
>
>
>
> 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
>
>
>
> End Sub
>
> "Tom Ogilvy" wrote:
>
> > Try it like this. You IF and Endif statements are not constructed as
you
> > think they are:
> >
> >
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >