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 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
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.
1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
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
0
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
0
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
0