Highlight Cells Based on Drop Down List Selection [Solved/Closed]

wliang 41 Posts Thursday June 16, 2011Registration date May 7, 2014 Last seen - Oct 3, 2012 at 02:59 AM - Latest reply: wliang 41 Posts Thursday June 16, 2011Registration date May 7, 2014 Last seen
- Oct 8, 2012 at 07:58 PM
Good day,

I would like to have a row highlighted in color based on the drop down list selection. I have inserted the following in Worksheet but I encountered "Out of Memory" error when I run first run macro "SeqLine1".

My sequence of execution is macro "SeqLine1" (located in sheet LINE_1) is executed first to get a plan (i.e. SUMMARY_1). The drop down list is in SUMMARY_1 column F. When I make a selection from the drop down list, that row of data (until column F) will be highlighted in color.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
Select Case Target
Case "No closers"
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 8
Case "Machine breakdown"
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 3
Case "Absenteeism"
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 4
Case "No packing materials"
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 5
Case "Wrong closers received"
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 6
Case "Miscellaneous"
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 7
Case Else
Target.Offset(, -5).Resize(, 5).Interior.ColorIndex = 0
End Select
End Sub

Herewith a copy of my file.
http://speedy.sh/8wyZ2/6000-SEQ-PLAN-PACKAGING-Rev2.0.xlsm

Appreciate the help from everyone on this.

Thank you.

Best regards,
wliang
See more 

4 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 4, 2012 at 12:55 AM
1
Thank you
I did debugging your event code.
where is the target. from the validation arrow in sheet "summary 1 it looks it is in the column F which is column no.6
if you write target.offset(0,-6) this is error because it goes to the left of column A.I do not know whether you are thinking (0,-5)

(offset to step back or forwards)

in the immediate window if you type
?Target.Offset(, -5).Resize(, 5).Address
and hit enter key you will get
$A$4:$E$4

I think this what you are thinking
note offset is minus five and resize is +5(or +6)

you try with this modification.

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1679 users this month

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 3, 2012 at 05:32 AM
0
Thank you
IS this not something like nested if and in some version maximum is 7. check whether this is the problem . I remember seeing in the web some way to get out of the situation
wliang 41 Posts Thursday June 16, 2011Registration date May 7, 2014 Last seen - Oct 3, 2012 at 07:45 AM
0
Thank you
Hi venkat1926,
Thanks for your response. I have checked the nested if and tried to limit the selection to 3 but still having the same error. I tried searching in the Web on this but can't get much info.

Is it possible to change the code? Instead of putting them directly into a worksheet, is it possible to create them into a module? Any other way to do this highlighting a row based on selection from drop down list?

Please advise. Appreciate your help on this.

Thank you.

Best regards,
wliang
wliang 41 Posts Thursday June 16, 2011Registration date May 7, 2014 Last seen - Oct 8, 2012 at 07:58 PM
0
Thank you
Hi venkat1926,

Thanks for your advice. I have tried some modifications on the codes and it seems to work fine now.

Once again thanks for your help.

Best regards,
wliang