| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Event Macros Help Needed | Excel Discussion (Misc queries) | |||
| Conditional Formatting and Macros in Excel | New Users to Excel | |||
| Conditional formatting based on decision to enable/disable macros? | Excel Discussion (Misc queries) | |||
| Mental Block! - Event Change Conditional Formatting | Excel Programming | |||
| Event for conditional formatting? | Excel Programming | |||
Linear Mode
