Highlight Cells Based on Drop Down List SelectionSolved/Closed

Question

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

venkat1926 · Answer

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 · Answer

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

venkat1926 · Answer

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.

wliang · Answer

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

Highlight Cells Based on Drop Down List Selection

4 responses

Similar discussions

Subscribe To Our Newsletter!