Highlight Cells Based on Drop Down List Selection

[Solved/Closed]
Report
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
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

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014

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
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014

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