Highlight Cells Based on Drop Down List Selection

Solved/Closed
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Oct 3, 2012 at 02:59 AM
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - 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://ww1.c9rzz.gce8xz.ygwt.online/?sov=96448523&hid=cmecskqmoeooo&cntrl=00000&pid=14497&redid=49184&gsid=453&campaign_id=12&p_id=14497&id=XNSX.-r49184-t453&impid=559a73c2-3c09-11e8-91b6-cae258990218&sub1=20201204-0401-258e-a087-763d1a10328c

Appreciate the help from everyone on this.

Thank you.

Best regards,
wliang

4 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 4, 2012 at 12:55 AM
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.
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 3, 2012 at 05:32 AM
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 Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Oct 3, 2012 at 07:45 AM
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 Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Oct 8, 2012 at 07:58 PM
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