Conditional Formatting using Event Macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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)
 
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
 
You need to post your change event code.

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

Bob
 
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
 
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
Click to expand...


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.
 
Back
Top