Hiding rows based on a cell value with multiple values

[ERed1]

Hey everyone,
I'm trying to write a code that hides rows based on cell value, but with a twist. The cell that it is based off of is a drop down. I have it to where you can select multiple options within the cell. I have a list of colors in the drop down and at the end I have a "Special Color" option. I need the "Special Color" option to appear only when selected. The drop down list is in cell B2 and the Special color is going to be written in B3. The entire row can be hidden. Here is the code I am using for being able to select multiple options in the drop down.

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$B$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If

 

[sijpie]

Hi ERed1. Please when you post code, use code tags around your code (see example in red below)

I don't understand what your code is doing or trying to achieve. Where is it residing? Is it in a worksheet module, worksheet_change() perhaps?

There is also a bug in this code: you are setting EnableEvents to False, which switches this off. But you don't reset it to True later on. So your code will only work once. After that Excel won't react to events! This is a 'sticky' property. You always need to reset EnableEvents.

And why are you calling the Undo method?

Furthermore I don't understand your question.
You have a listbox with colours listed (say red, blue and green)
Then there could be another item added 'Special Color'.
You want this to appear 'only if selected'. What needs to be selected? You can't select it if it isn't there in the first place