Ask a question Report

Highlight Cells Based on Drop Down List Selection [Solved]

wliang 37Posts Thursday June 16, 2011Registration date October 8, 2012Last seen - Latest answer on Oct 9, 2012 12:58am BST

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

Answer

venkat1926 1485Posts Sunday June 14, 2009Registration date ContributorStatus April 29, 2013Last seen Oct 4, 2012 5:55am BST

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.

Add comment

Answer

venkat1926 1485Posts Sunday June 14, 2009Registration date ContributorStatus April 29, 2013Last seen Oct 3, 2012 10:32am BST

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

Add comment

Answer

wliang 37Posts Thursday June 16, 2011Registration date October 8, 2012Last seen Oct 3, 2012 12:45pm BST

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

Add comment

Answer

wliang 37Posts Thursday June 16, 2011Registration date October 8, 2012Last seen Oct 9, 2012 12:58am BST

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

Add comment

Reply to this topic

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Highlight Cells Based on Drop Down List Selection [Solved]

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!