I found this nice code that keeps a drop down's info and adds to it with a "," then the next input. When you select the same drop down item it gets rid of that item from the list in the cell. However i need to add a third option.
I need it to add a new item on the first item choice. The second choice of the same item needs to turn the text for that one item red in the list. (Eg: chair, lamp, dog. Only Lamp would be red if it was chosen a second time.) and then on the third choice it gets rid of the choice in the list as the code does currently. I was trying change the dim of newval to a Font.Color = RGB (255, 0 , 0) but i can't seem to get the Dim to work. Any suggestions.
Code below:
Any help or guidance would be greatly appreciated.
Thanks
I need it to add a new item on the first item choice. The second choice of the same item needs to turn the text for that one item red in the list. (Eg: chair, lamp, dog. Only Lamp would be red if it was chosen a second time.) and then on the third choice it gets rid of the choice in the list as the code does currently. I was trying change the dim of newval to a Font.Color = RGB (255, 0 , 0) but i can't seem to get the Dim to work. Any suggestions.
Code below:
PHP:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim lUsed As Long Dim clrRed As Long
If Target.Count > 1 Then GoTo exitHandler
If Target.Column = 1 Then
On Error Resume Next
Set rngDV = Target.EntireColumn.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Any help or guidance would be greatly appreciated.
Thanks