Multiple Entries Selected On Drop Down List highlights row

[hombrelobo]

Hi All,

Can someone please point me in the right direction, still a rookie with Excel! Please see image below for details.

Goal is for when cell E2 has multiple items selected from the drop down list ( in this case more than 1 medication) it would highlight the row . Already enabled drop down list for multiple entries.

Would I use a formula with conditional formatting

 

[Fluff]

Hi & welcome to MrExcel.
When multiple items are selected what separator is used?

 

[hombrelobo]

Hi and thank you.

I inserted the following VBA Code to enable to the mulitple selection of items from the drop down list. Not sure what "separator" is used.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 5 Or Target.Column = 6 Or Target.Column = 7 Or Target.Column = 8 Or Target.Column = 9 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
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Fluff]

Ok, select A2:E last row & in conditional formatting, new rule, use a formula & use

=FIND(CHAR(10),$E2)

 

[hombrelobo]

AWESOME it worked I will have to study the "FIND" formula! My next step would be to figure out how to enable via VBA how to deselect an item on the cell once it longer being used. (med is no longer being taken!)

Thanks again!!

 

[Fluff]

You're welcome & thanks for the feedback.